System and method for automatic vertical decomposition of a table for improving input/output and memory utilization in a database

• US 9,922,090 B1
• Filed: 03/27/2012
• Issued: 03/20/2018
• Est. Priority Date: 03/27/2012
• Status: Expired due to Fees

• Current Assignee: Actian Corporation
• Original Assignee: Actian Netherlands BV (HCL Technologies Limited)
• Inventors: Zukowski, Marcin, Li, Hui
• Primary Examiner(s): Bullock, Joshua
• Application Number:
  US13/431,890
• Time in Patent Office: 2,184 Days
• Field of Search: 707693
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement

Abstract

A system and method for improving input/output and memory utilization in a database are described. The system may implement an automatic vertical table decomposition process to achieve the improved input/output, storage and memory utilization in a database.

Claims

1. A database system, comprising:

a store having a plurality of tables;

wherein each table has a plurality of wide attributes and the plurality of attributes are partitioned into one or more groups for physical storage;

a computer having a database engine connected to the store that manipulates the tables; and

the database engine having a table decomposition unit that automatically decomposes the table having a schema into a plurality of smaller groups having the same schema for storage in the store by splitting the table by column width based on a maximum number of columns in each group into the plurality of smaller groups, creating separate groups for DSM columns in the table if a maximum number of groups is not exceeded by the splitting the table by column width and further splitting one or more of the smaller groups by column width if the maximum number of groups is not exceeded by the creation of the separate groups for DSM columns and returning a decomposed table structure to the store.

2. The system of claim 1, wherein the table decomposition unit decomposes the table that has a high number of attributes into a plurality of smaller groups.

3. The system of claim 2, wherein the table decomposition unit combines groups to reduce the number of groups.

4. The system of claim 3, wherein table decomposition unit combines groups when a number of groups is more than a maximum number of groups.

5. The system of claim 3, wherein the table decomposition unit splits the PaxGroup.

6. The system of claim 5, wherein the table decomposition unit splits the PaxGroup when a number of one or more separate groups and one or more smaller groups is less than a maximum number of PaxGroups.

7. The system of claim 1, wherein the table decomposition unit performs one of decomposes the table that has a high number of attributes into a plurality of smaller groups, combines groups to reduce the number of groups and splits the PaxGroup based on one of a user controlled criteria and a predetermined criteria.

8. The system of claim 7, wherein the predetermined criteria is one of a maximum number of physical groups per table, a maximum width of attributes in a single group and a maximum number of attributes in a single group.

9. The system of claim 8, wherein the maximum number of physical groups per table is determined based on available memory of a computer executing the database engine.

10. The system of claim 8, wherein one of the maximum number of physical groups per table and the maximum width of attributes in a single group is determined based on parameters of an input/output system.

11. The system of claim 10, wherein the parameters of an input/output system are one of a block size and an input/output speed.

12. The system of claim 7, wherein the predetermined criteria is provided by the user.

13. The system of claim 7, wherein the predetermined criteria is dynamically determined based on an application environment.

14. The system of claim 7, wherein the predetermined criteria is an expected number of records in the table.

15. The system of claim 14, wherein the table decomposition unit creates one or more many-column groups for tables with a small number of records.

16. The system of claim 14, wherein the table decomposition unit creates multiple few-column or single-column groups for tables with a large number of records.

17. A method for managing a database using a computer implemented database system that has a store having a plurality of tables and a database engine connected to the store that manipulates the tables, the method comprising:

automatically decomposing, on a computer implemented database system, a table having a schema with a plurality of wide attributes into a plurality of smaller groups having the same schema for the storage in the store by splitting the table by column width based on a maximum number of columns in each group into the plurality of smaller groups;

creating separate groups for DSM columns in the table if a maximum number of groups is not exceeded by the splitting the table by column width;

splitting one or more of the smaller groups by column width if the maximum number of groups is not exceeded by the creation of the separate groups for DSM columns; and

providing the decomposed table structure to the store.

18. The method of claim 17, wherein automatically decomposing the table further comprises decomposing the table that has a high number of attributes into a plurality of smaller groups.

19. The method of claim 18 further comprising combining groups to reduce the number of groups.

20. The method of claim 19, wherein combining groups is done when a number of groups is more than a maximum number of groups.

21. The method of claim 19 further comprising splitting a PaxGroup.

22. The method of claim 21, wherein splitting the PaxGroup occurs when a number of one or more separate groups and one or more smaller groups is less than a maximum number of PaxGroups.

23. The method of claim 17, wherein automatic decomposing further comprises one of decomposing the table that has a high number of attributes into a plurality of smaller groups, combining groups to reduce the number of groups and splitting the PaxGroup based on one of a user controlled criteria and a predetermined criteria.

24. The method of claim 23, wherein the predetermined criteria is one of a maximum number of physical groups per table, a maximum width of attributes in a single group and a maximum number of attributes in a single group.

25. The method of claim 24 further comprising determining the maximum number of physical groups per table based on available memory of a computer executing the database engine.

26. The method of claim 24 further comprising determining one of the maximum number of physical groups per table and the maximum width of attributes in a single group based on parameters of an input/output system.

27. The method of claim 26, wherein the parameters of an input/output system are one of a block size and an input/output speed.

28. The method of claim 23, wherein the predetermined criteria is provided by the user.

29. The method of claim 23 further comprising dynamically determining the predetermined criteria based on an application environment.

30. The method of claim 23, wherein the predetermined criteria is an expected number of records in the table.

31. The method of claim 30 further comprising creating one or more many-column groups for tables with a small number of records.

32. The method of claim 30 further comprising creating multiple few-column or single-column groups for tables with a large number of records.