Data allocation containers in a partitioned table of a computer database system for holding data based on usage

• US 9,424,300 B2
• Filed: 09/25/2013
• Issued: 08/23/2016
• Est. Priority Date: 09/25/2013
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Konik, Rafal P., Mittelstadt, Roger A., Muras, Brian R., Theuer, Mark W.
• Primary Examiner(s): Perveen, Rehana
• Assistant Examiner(s): BUI, THUY T
• Application Number:
  US14/036,058
• Publication Number:
  US 20150088912A1
• Time in Patent Office: 1,063 Days
• Field of Search: 707/751
• US Class Current: 1/1
• 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/2477 Temporal data queries

Abstract

An apparatus and method utilize partitioned database tables divided into data allocation containers (DACs) where data is placed into the DACs based on usage of the data in past queries. Records that are used most often are placed together and records that are used less often are placed together to improve database performance. In preferred embodiments, a database manager determines where to place data into the DACs based on how often the data is selected by a database query using a DAC selection ratio (DSR). The database manager may determine when to perform table maintenance to move rows of data to the appropriate DACs based on a timestamp or last check date (LCD) stored in the database.

Claims

1. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a database residing in the memory having data in at least one partitioned database table divided into a plurality of data allocation containers (DACs);

a database manager with a DAC manager residing in the memory and executed by the at least one processor that determines which of the plurality of DACs to place rows of data into based on usage of the rows of data in past queries from data usage information stored in the rows of the data of the DACs, and

wherein the data usage information comprises a counter and the DAC manager increments the counter for a number of times selected for each selected row by the query, and computes a DAC selection ratio (DSR) for each selected row by the query from a quotient of a number of queries made to the partitioned table and the number of times selected.

2. The apparatus of claim 1 wherein the plurality of DACs include a high priority DAC placed in a memory device with a faster access time and a low priority DAC placed in a memory device with a slower access time.

3. The apparatus of claim 1 wherein the DAC manager increments a counter for a number of times inspected that indicates how many times the row of data was inspected but not selected.

4. The apparatus of claim 1 wherein the DAC manager determines if the DSR is within a predetermined range for a current DAC, and moves the row of data to a different DAC when the data selection ratio is not within the predetermined range for the current DAC.

5. The apparatus of claim 1 wherein the data usage information comprises a last accessed time stamp and the DAC manager updates the last accessed time stamp for each selected row by the query;

and moves the selected row of data to an different DAC based on the last accessed time stamp.

6. The apparatus of claim 1 a wherein the data usage information comprises a time based bit map and the DAC manager updates the time based bit map for each selected row by the query, and moves the selected row of data to a different DAC based on the time based bit map.

7. The apparatus of claim 6 wherein the time based bit map is divided into a time base chosen from the following:

years, months, weeks, days, hours, minutes, or seconds.

8. A program product comprising:

a database manager with a data allocation container (DAC) manager, wherein the DAC manager determines which of a plurality of DACs to place rows of data into based on usage of the rows of data in past queries from data usage information stored in the rows of the data of the DACs, wherein the plurality of DACs include a high priority DAC placed in a memory device with a faster access time and a low priority DAC placed in a memory device with a slower access time;

wherein the DAC manager increments a counter for a number of times selected for each selected row by the query, and computes a DAC selection ratio (DSR) for each selected row by the query from a quotient of a number of queries made to the partitioned table and the number of times selected; and

non-transitory computer-readable media bearing the database manager and the DAC manager.

9. The program product of claim 8 wherein the DAC manager updates a last accessed time stamp for each selected row by the query;

and moves the selected row of data to an appropriate DAC based on the last accessed time stamp.

10. The program product of claim 8 wherein the DAC manager updates a time based bit map for each selected row by the query, and moves the selected row of data to an appropriate DAC based on the time based bit map.

11. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a database residing in the memory having data in at least one partitioned database table divided into a plurality of data allocation containers (DACs);

a database manager with a DAC manager residing in the memory and executed by the at least one processor that determines which of the plurality of DACs to place rows of data into based on data usage information stored in each row of data of the DACs, wherein the data usage information stored in each row includes a counter for a number of times each row was selected in past queries;

wherein the DAC manager increments the counter for a number of times selected for each selected row by the query, and computes a DAC selection ratio (DSR) for each selected row by the query from a quotient of a number of queries made to the partitioned table and the number of times selected;

wherein the DAC manager increments a counter for a number of times inspected that indicates how many times the row of data was inspected but not selected; and

wherein the DAC manager determines if the DSR is within a predetermined range for a current DAC, and moves the row of data to a different DAC when the data selection ratio is not within the predetermined range for the current DAC.

12. The apparatus of claim 11 wherein the plurality of DACs include a high priority DAC placed in a memory device with a faster access time and a low priority DAC placed in a memory device with a slower access time.

13. The apparatus of claim 11 wherein the data usage information comprises a last accessed time stamp and the DAC manager updates the last accessed time stamp for each selected row by the query;

and moves the selected row of data to an different DAC based on the last accessed time stamp.

14. The apparatus of claim 11 a wherein the data usage information comprises a time based bit map and the DAC manager updates the time based bit map for each selected row by the query, and moves the selected row of data to a different DAC based on the time based bit map.

15. The apparatus of claim 14 wherein the time based bit map is divided into a time base chosen from the following:

years, months, weeks, days, hours, minutes, or seconds.