Optimizing tables with too many columns in a database

Patent metadata

Field	Value
Publication number	US-9946741-B2
Application number	US-201514719433-A
Country	US
Kind code	B2
Filing date	May 22, 2015
Priority date	May 22, 2015
Publication date	Apr 17, 2018
Grant date	Apr 17, 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 system for database optimization, comprising:
a database rebuild module comprising a processor configured to detect a plurality of columns in an unoptimized database that co-appear in queries to the database and to compact the plurality of columns into a single hyper-column 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
a database interface module configured to accepts queries according to a schema for the unoptimized database and to translate said queries into queries according to a schema for the optimized database.

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

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

4. The system of claim 1 , wherein the database rebuild module is further configured to generate an index for the hyper-column.

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

6. The system of claim 1 , wherein the database rebuild module is further configured to compress entries in the hyper-column if the entries in the hyper-column consist of textual data.

7. The system of claim 1 , wherein the database rebuild module is further configured to performing clustering across all columns in the database to detect columns that co-appear in queries.

8. The system of claim 7 , wherein the database rebuild module is further configured to perform clustering using an approximation of K-minimum cut.

9. The system of claim 1 , wherein the database interface module is further configured to provide a virtual view of the database according to the schema for the unoptimized database.

10. A computer readable storage medium comprising a computer readable program for database optimization, wherein the computer readable program when executed on a computer causes the computer to perform the steps of:
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.

11. The system of claim 7 , further comprising representing the columns in the unoptimized database as a unidirected graph, with nodes of the graph representing columns and with edges of the graph representing a co-appearing frequency for the connected columns.

Assignees

• IBM

Inventors

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

Classifications

• G06F17/30454

  Physics · mapped topic

• G06F17/30498

  Physics · mapped topic

• G06F17/30315 Primary

  Physics · mapped topic

• G06F17/30339

  Physics · mapped topic

• G06F17/30327

  Physics · mapped topic