Optimizing tables with too many columns in a database

Patent metadata

Field	Value
Publication number	US-10042872-B2
Application number	US-201514746465-A
Country	US
Kind code	B2
Filing date	Jun 22, 2015
Priority date	May 22, 2015
Publication date	Aug 7, 2018
Grant date	Aug 7, 2018

Abstract

Methods and systems for database optimization include detecting a plurality of columns in an unoptimized database that co-appear in queries to the database. The plurality of columns are compacted into a single hyper-column to form an optimized database. Each entry in the hyper-column includes data from a corresponding entry in each of the plurality of columns. An interface is generated for the optimized database that accepts queries according to a schema for the unoptimized database and translates the queries into queries according to a schema for the optimized database.

First claim

The invention claimed is:

1. A method for database optimization, comprising:
detecting a plurality of columns in an unoptimized database that co-appear in queries to the database;
compacting the plurality of columns into a single hyper-column using a processor to form an optimized database, wherein each entry in the hyper-column comprises data from a corresponding entry in each of the plurality of columns; and
generating an interface for the optimized database that accepts queries according to a schema for the unoptimized database and translates said queries into queries according to a schema for the optimized database.

2. The method of claim 1 , wherein the plurality of columns co-appear in select clauses.

3. The method of claim 1 , wherein the plurality of columns co-appear in where clauses.

4. The method of claim 1 , further comprising generating an index for the hyper-column.

5. The method of claim 4 , wherein entries for the hyper-column comprise only numerical data and wherein the index is an R-tree.

6. The method of claim 1 , further comprising compressing entries in the hyper-column if the entries in the hyper-column consist of textual data.

7. The method of claim 1 , wherein detecting the plurality of columns that co-appear in queries to the database comprises performing clustering across all columns in the database.

8. The method of claim 7 , wherein performing clustering comprises an approximation of K-minimum cut.

9. The method of claim 1 , wherein generating the interface comprises generating a virtual view of the database according to the schema for the unoptimized database.

10. A method for database optimization, comprising:
detecting a plurality of columns in an unoptimized database that co-appear in at least one of where and select queries to the database by clustering across all columns according to co-appearance using an approximation of K-minimum cut;
compacting the plurality of columns into a single hyper-column using a processor to form an optimized database, wherein each entry in the hyper-column comprises data from a corresponding entry in each of the plurality of columns;
generating an index for the hyper-column, comprising generating an R-tree index if the hyper-column consists of numerical data and generating a B-tree index if the hyper-column consists of textual data;
compressing entries in the hyper-column if the entries in the hyper-column consist of textual data; and
generating an interface for the optimized database that accepts queries according to a schema for the unoptimized database and translates said queries into queries according to a schema for the optimized database, wherein said interface comprises a virtual view of the database according to the schema for the unoptimized database.

Assignees

• IBM

Inventors

• Chen Yaoliang
• Gong Min
• Ma Caiqin
• Wang Yu
• Xu Xiaomin
• Yan Junchi

Classifications

• G06F16/221 Primary

  Column-oriented storage; Management thereof · CPC title

• G06F16/2456

  Join operations · CPC title

• G06F16/24544

  Join order optimisation · CPC title

• G06F16/2246

  Trees, e.g. B+trees · CPC title

• G06F16/24537

  of operators · CPC title