Input/output efficiency for online analysis processing in a relational database

• US 8,719,312 B2
• Filed: 03/27/2012
• Issued: 05/06/2014
• Est. Priority Date: 03/31/2011
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Jin, Yi, Li, Lei, Wang, Li Li, Zhang, Wan Chuan
• Primary Examiner(s): Jami, Hares
• Application Number:
  US13/430,897
• Publication Number:
  US 20120254252A1
• Time in Patent Office: 770 Days
• Field of Search: 707/600, 707/602, 707/790, 707/797, 707/802, 707/803, 707/812
• US Class Current: 707/802
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Embodiments of the invention relate to improved input/output efficiency for online analysis processing in a relational database. An aspect of the invention includes selecting a table from a relational database. The table is split by column into sub-tables, with at least one of the sub-tables including at least two columns. Each sub-table is written into a corresponding table of a row-based storage database.

Claims

1. A method comprising:

selecting a table of a relational database;

splitting, by a computer, the selected table by column into sub-tables, wherein at least one of the sub-tables includes at least two columns; and

writing each of the sub-tables into corresponding tables of a row-based storage database, the writing including;

specifying one of the sub-tables as a base sub-table;

creating a row identifier (RID) for each row in the base sub-table;

adding the RIDs to each row of the base sub-table; and

adding the RIDs to each corresponding row of each of the sub-tables.

2. The method of claim 1, wherein the splitting comprises:

selecting one column in the table; and

grouping the selected column together with other columns from the table according to a frequency that the selected column and the other columns are jointly accessed.

3. The method of claim 1, further comprising:

indexing each of the sub-tables of the row-based storage database by using one of;

a B+ tree structure; and

a hash index;

assigning a row pointer corresponding to each row of the sub-tables to each leaf index entry; and

setting a RID value of each of the row pointers of every index entry to the RID of each corresponding row in the sub-tables of the index entry.

4. A data processing device comprising:

a computer processor configured to perform a method comprising;

selecting a table of a relational database;

splitting the selected table by column into sub-tables, wherein at least one of the sub-tables includes at least two columns; and

writing each of the sub-tables into corresponding tables of a row-based storage database, the writing including;

specifying one of the sub-tables as a base sub-table;

creating a row identifier (RID) for each row in the base sub-table;

adding the RIDs to each row of the base sub-table; and

adding the RIDs to each corresponding row of each of the sub-tables.

5. The data processing device of claim 4, wherein the splitting comprises:

selecting one column in the table; and

grouping the selected column together with other columns from the table according to a frequency that the selected column and the other columns are jointly accessed.

6. The data processing device of claim 4, wherein the method further comprises:

indexing each of the sub-tables of the row-based storage database by using one of;

a B+ tree structure; and

a hash index;

assigning a row pointer corresponding to each row of the sub-tables to each leaf index entry; and

setting a RID value of each of the row pointers of every index entry to the RID of each corresponding row in the sub-tables of the index entry.

7. A computer program product comprising:

a non-transitory computer readable storage medium having computer readable program code embodied therewith, the computer readable program code comprising;

computer readable program code configured for;

selecting a table of a relational database;

splitting the selected table by column into sub-tables, wherein at least one of the sub-tables includes at least two columns; and

writing each of the sub-tables into corresponding tables of a row-based storage database, the writing including;

specifying one of the sub-tables as a base sub-table;

creating a row identifier (RID) for each row in the base sub-table;

adding the RIDs to each row of the base sub-table; and

adding the RIDs to each corresponding row of each of the sub-tables.

8. The computer program product of claim 7, wherein the splitting comprises:

selecting one column in the table; and

grouping the selected column together with other columns from the table according to a frequency that the selected column and the other columns are jointly accessed.

9. The computer program product of claim 7, wherein the computer readable program code is further configured for:

indexing each of the sub-tables of the row-based storage database by using one of;

a B+ tree structure; and

a hash index;

assigning a row pointer corresponding to each row of the sub-tables to each leaf index entry; and

setting a RID value of each of the row pointers of every index entry to the RID of each corresponding row in the sub-tables of the index entry.