Database-transparent near online archiving and retrieval of data

• US 9,152,683 B2
• Filed: 10/05/2010
• Issued: 10/06/2015
• Est. Priority Date: 10/05/2010
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Koenig, Edgardo Gildo, Moldowan, Hans-Juergen, Rech, Thomas
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US12/898,252
• Publication Number:
  US 20120084316A1
• Time in Patent Office: 1,827 Days
• Field of Search: 707/999.102, 707/705, 707/694
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

Method, program products and apparatus are disclosed for optimizing access to a data table. A value-existence structure is used to determine whether the data table contains rows for a given database query. The value-existence structure may be useful, for example, for managing transparent access to an online database and a nearline database.

Claims

1. A computer implemented method of managing access to rows of a database table, the method comprising:

receiving a database query from a database client, the database query comprising a command to search for rows of the database table containing a given value in a given column, wherein the rows of the database table are stored in a first data table on a first storage media and a second data table on a second storage media, wherein the first storage media has a lower access speed than the second storage media;

determining whether a value-existence structure contains the given value, the value-existence structure comprising a list of distinct values in the given column stored in the first data table,

upon determining the value-existence structure contains the given value retrieving from the first data table the rows containing the given value and transmitting the rows of the first data table to the database client; and

retrieving from the second data table any rows containing the given value in the given column and transmitting any retrieved rows of the second data table to the database client.

2. The method of claim 1, further comprising:

upon determining the value-existence structure does not contain the given value, transmitting an indication that there do not exist rows in the first data table containing the given value in the given column.

3. The method of claim 1, further comprising

generating the value-existence structure for the first data table by analyzing the content of the first data table.

4. The method of claim 1, wherein the indicator identifying the columns in which a distinct value is stored is a bitchain, wherein a bit is associated with each column in the first data table, wherein a bit in the bitchain is set to a first logic value if the respective column contains the distinct value, and wherein a bit in the bitchain is set to a second logic value if the respective column does not contain the distinct value.

5. The method of claim 1, further comprising:

detecting rows in the second data table, which have not been accessed over a predetermined time interval;

moving the detected rows from the second data table to the first data table; and

updating the value-existence structure, if a row has been moved from the second data table to the first data table.

6. The method of claim 1, comprising:

detecting rows in the first data table, which have been accessed;

moving the accessed rows from the first data table to the second data table; and

updating the value-existence structure, if a row has been moved from the first data table to the second data table.

7. A non-transitory computer readable storage medium storing a program, which, when executed on a processor, performs an operation for managing access to rows of a database table, the operation comprising:

receiving a database query from a database client, the database query comprising a command to search for rows of the database table containing a given value in a given column, wherein the rows of the database table are stored in a first data table on a first storage media and a second data table on a second storage media, wherein the first storage media has a lower access speed than the second storage media;

determining whether a value-existence structure contains the given value, the value-existence structure comprising a list of distinct values in the given column stored in the first data table,

upon determining the value-existence structure contains the given value retrieving from the first data table the rows containing the given value and transmitting the rows of the first data table to the database client; and

retrieving from the second data table any rows containing the given value in the given column and transmitting any retrieved rows of the second data table to the database client.

8. The computer readable storage device of claim 7, wherein the operation further comprises:

upon determining the value-existence structure does not contain the given value, transmitting an indication that there do not exist rows in the first data table containing the given value in the given column.

9. The computer readable storage device of claim 7, wherein the operation further comprises:

generating the value-existence structure for the first data table by analyzing the content of the first data table.

10. The computer readable storage device of claim 7, wherein the indicator identifying the columns in which a distinct value is stored is a bitchain, wherein a bit is associated with each column in the first data table, wherein a bit in the bitchain is set to a first logic value if the respective column contains the distinct value, and wherein a bit in the bitchain is set to a second logic value if the respective column does not contain the distinct value.

11. The computer readable storage device of claim 7, wherein the operation further comprises:

detecting rows in the second data table, which have not been accessed over a predetermined time interval;

moving the detected rows from the second data table to the first data table; and

updating the value-existence structure, if a row has been moved from the second data table to the first data table.

12. The computer readable storage device of claim 7, wherein the operation further comprises:

detecting rows in the first data table, which have been accessed; and

moving the accessed rows from the first data table to the second data table; and

updating the value-existence structure, if a row has been moved from the first data table to the second data table.

13. A system, comprising:

a processor; and

a memory storing a database management system tool for managing access to a first data table, which when executed on the processor is configured to perform an operation, comprising;

receiving a database query from a database client, the database query comprising a command to search for rows of the database table containing a given value in a given column, wherein the rows of the database table are stored in a first data table on a first storage media and a second data table on a second storage media, wherein the first storage media has a lower access speed than the second storage media,

determining whether a value-existence structure contains the given value, the value-existence structure comprising a list of distinct values in the given column stored in the first data table,

upon determining the value-existence structure contains the given value retrieving from the first data table the rows containing the given value and transmitting the rows of the first data table to the database client, and

retrieving from the second data table any rows containing the given value in the given column and transmitting any retrieved rows of the second data table to the database client.

14. The system of claim 13, wherein the operation further comprises:

upon determining the value-existence structure does not contain the given value, transmitting an indication that there do not exist rows in the first data table containing the given value in the given column.

15. The system of claim 13, wherein the operation further comprises:

generating the value-existence structure for the first data table by analyzing the content of the first data table.

16. The system of claim 13, wherein the indicator identifying the columns in which a distinct value is stored is a bitchain, wherein a bit is associated with each column in the first data table, wherein a bit in the bitchain is set to a first logic value if the respective column contains the distinct value, and wherein a bit in the bitchain is set to a second logic value if the respective column does not contain the distinct value.

17. The system of claim 13, wherein the operation further comprises:

detecting rows in the second data table, which have not been accessed over a predetermined time interval;

moving the detected rows from the second data table to the first data table; and

updating the value-existence structure, if a row has been moved from the second data table to the first data table.

18. The system of claim 13, wherein the operation further comprises:

detecting rows in the first data table, which have been accessed; and

moving the accessed rows from the first data table to the second data table; and

updating the value-existence structure, if a row has been moved from the first data table to the second data table.