Method and system for optimizing data access in a database using multi-class objects

Patent metadata

Field	Value
Publication number	US-9805077-B2
Application number	US-37146909-A
Country	US
Kind code	B2
Filing date	Feb 13, 2009
Priority date	Feb 19, 2008
Publication date	Oct 31, 2017
Grant date	Oct 31, 2017

Abstract

Methods for optimizing data access in a row-oriented relational database containing data sets having attributes using a computer are presented the method including: causing a computer to analyze a database workload to determine an access frequency for each of the attributes; causing the computer to assign each of the attributes to a priority classes corresponding with the access frequency, where the priority classes include a higher priority class and a lower priority class, and where a higher priority class corresponds with a higher access frequency and a lower priority class corresponds with a lower access frequency; causing the computer to store the attributes in accordance with the classes, where the attributes assigned to the higher priority class are stored in a high priority storage medium, and where the attributes assigned to a lower priority class are stored in a low priority storage medium.

First claim

What is claimed is:

1. A method for optimizing data access in a row-oriented relational database containing a plurality of data sets having a plurality of attributes using a computer, the method comprising:
causing a computer to analyze a database workload to determine an access frequency for each of the plurality of attributes, wherein each of the plurality of attributes corresponds with a column, wherein the plurality of data sets correspond with a plurality of rows, and wherein the analysis is executed inside a database engine where column data associated with the column is accessed;
causing the computer to assign each of the plurality of attributes to a plurality of priority classes corresponding with the access frequency, wherein the plurality of priority classes includes at least a higher priority class and a lower priority class, and wherein a higher priority class corresponds with a higher access frequency and a lower priority class corresponds with a lower access frequency;
causing the computer to store the plurality of attributes in a plurality of modified datasets in accordance with the plurality of priority classes, wherein the plurality of attributes assigned to the higher priority class are stored in a high priority storage medium, wherein the plurality of attributes assigned to a lower priority class are stored in a low priority storage medium, and wherein rows corresponding with the plurality of modified datasets in the row-oriented relational database are broken up into multiple row fragments for storage in different pages by column corresponding with the plurality of attributes such that the row-oriented relational database is reorganized and data access to the database is optimized.

2. The method of claim 1 , wherein the plurality of attributes assigned to the high priority class are reallocated to a plurality of high class fragments, and wherein the plurality of attributes assigned to the low priority class are reallocated to a plurality of low class fragments.

3. The method of claim 2 , wherein the plurality of high class fragments are stored in the high priority storage medium, and wherein the plurality of low class fragments are stored in the low priority storage medium.

4. The method of claim 3 , wherein a memory space corresponding with the high priority class and the low priority class is reallocated for additional storage.

5. The method of claim 2 , wherein a memory space corresponding with the high class and the low class is reorganized in-place by reallocating the plurality of high class fragments and the plurality of low class fragments page-by-page.

6. The method of claim 2 , further comprising:
causing the computer to manage a buffer pool by evicting the plurality of low class fragments from the buffer pool before the plurality of high class fragments in response to the buffer pool becoming full.

7. The method of claim 1 , further comprising:
causing the computer to substantially continuously monitor the database workload to optimize the row-oriented relational database such that the method is responsive to a change in a database workload pattern, wherein the database workload pattern corresponds with a use of at least one application.

8. The method of claim 1 , further comprising:
causing the computer to analyze the database workload inside a database engine.

9. The method of claim 1 , wherein causing the computer to store the plurality of attributes further comprises:
causing the computer to store the plurality of attributes concurrently while a query is issued against the row-oriented relational database.

10. The method of claim 1 , further comprising:
causing the computer to manage a buffer pool by evicting the plurality of attributes assigned to the lower priority class from the buffer pool before the plurality of attributes assigned to the higher priority class in response to the buffer pool becoming full.

11. A system of computer hardware for optimizing data access in a row-oriented relational database, the row-oriented relational database including a plurality of data sets each dataset having a plurality of attributes, the system comprising:
a processor;
an access monitoring system for monitoring database workload to determine an access frequency for each of the plurality of attributes, wherein each of the plurality of attributes corresponds with a column, wherein the plurality of data sets correspond with a plurality of rows, and wherein the monitoring is executed inside a database engine where column data associated with the column is accessed;
a classification system for assigning the plurality of attributes to a plurality of classes corresponding with the access frequency, wherein the plurality of classes include at least a high priority class and a low priority class; and wherein a higher priority class corresponds with a higher access frequency and a lower priority class corresponds with a lower access frequency;
an attribute storage system for storing the plurality of attributes in a plurality of modified datasets in accordance with the plurality of priority classes, wherein the plurality of attributes assigned to the higher priority class are stored in a high priority storage media, wherein the plurality of attributes assigned to a lower priority lass are stored in a low priority storage media, and wherein rows corresponding with the plurality of modified datasets in the row-oriented relational database are broken up into multiple row fragments for storage in different pages by column corresponding with the plurality of attributes such that the row-oriented relational database is reorganized and data access to the database is optimized.

12. The system of claim 11 , wherein the attribute storage system is further configured for separately storing a plurality of high class fragments corresponding with the plurality of attributes assigned to the high priority class and for separately storing a plurality of low class fragments corresponding with the plurality of attributes assigned to the low priority class.

13. The system of claim 12 , wherein the attribute storage system is further configured for reallocating a memory space corresponding with the high priority class and the low priority class for additional storage.

14. The system of claim 11 , wherein the plurality of storage media comprises at least:
high priority storage medium; and
low priority storage medium.

15. A computer program product for optimizing data access in a row-oriented relational database containing a plurality of data sets having a plurality of attributes using a computing device, the computer program product comprising:
a non-transitory computer readable medium;
first program instructions for analyzing a database workload to determine an access frequency for each of the plurality of attributes wherein each of the plurality of attributes corresponds with a column, wherein the plurality of data sets correspond with a plurality of rows, and wherein the analysis is executed inside a database engine where column data associated with the column is accessed;
second program instructions for assigning each of the plurality of attributes to a plurality of priority classes corresponding with the access frequency, wherein the plurality of priority classes includes at least a higher priority class and a lower priority class, and wherein a higher priority class corresponds with a higher access frequency and a lower priority class corresponds with a lower access frequency;
third program instructions for storing the plurality of attributes in a plurality of modified datasets in accordance with the plurali

Assignees

• Draese Oliver
• Staebler Benno
• Steinbach Torsten
• Stolze Knut
• IBM

Inventors

• Draese Oliver
• Staebler Benno
• Steinbach Torsten
• Stolze Knut

Classifications

• G06F16/221 Primary

  Column-oriented storage; Management thereof · CPC title

• G06F17/30315 Primary

  Physics · mapped topic