DATABASE-TRANSPARENT NEAR ONLINE ARCHIVING AND RETRIEVAL OF DATA

• US 20120084316A1
• Filed: 10/05/2010
• Published: 04/05/2012
• Est. Priority Date: 10/05/2010
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Koenig, Edgardo Gildo, Moldowan, Hans-Juergen, Rech, Thomas
• Granted Patent: US 9,152,683 B2
• Time in Patent Office: Days
• US Class Current: 707/769
• 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 a first data table, the method comprising:

receiving a database query from a database client, the database query comprising a command to search for one or more matching rows containing a given value in a given column;

verifying if a value-existence structure contains the given value, the value-existence structure comprising a list of distinct values stored in the first data table, wherein each distinct value in the value-existence structure is associated with a respective indicator identifying the column of the first data table in which the distinct value is stored; and

upon determining the value-existence structure contains the given value and the respective indicator indicates that the given value is stored in the given column, retrieving from the first data table the matching rows containing the given value in the given column, and transmitting the matching rows of the first 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, wherein the first data table is stored on a first storage media, wherein a second data table is stored on a second storage media, wherein the first storage media has a lower access speed than the second storage media, the method further comprising:

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

6. The method of claim 5, 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.

7. The method of claim 5, 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.

8. A computer readable storage medium storing a program, which, when executed on a processor, performs an operation for managing access to a first data table, the operation comprising:

receiving a database query from a database client, the database query comprising a command to search for one or more matching rows containing a given value in a given column;

verifying if a value-existence structure contains the given value, the value-existence structure comprising a list of distinct values stored in the first data table, wherein each distinct value in the value-existence structure is associated with a respective indicator identifying the column of the first data table in which the distinct value is stored; and

upon determining the value-existence structure contains the given value and the respective indicator indicates that the given value is stored in the given column, retrieve from the first data table the matching rows containing the given value in the given column, and transmitting the matching rows of the first data table to the database client.

9. The computer readable storage medium of claim 8, 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.

10. The computer readable storage medium of claim 8, wherein the operation further comprises:

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

11. The computer readable storage medium of claim 8, 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.

12. The computer readable storage medium of claim 8, wherein the operation further comprises:

retrieving from a second data table the matching rows containing the given value in the given column, and transmitting the matching rows of the second data table to the database client.

13. The computer readable storage medium of claim 12, 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.

14. The computer readable storage medium of claim 12, 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.

15. 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 one or more matching rows containing a given value in a given column,

verifying if a value-existence structure contains the given value, the value-existence structure comprising a list of distinct values stored in the first data table, wherein each distinct value in the value-existence structure is associated with a respective indicator identifying the column of the first data table in which the distinct value is stored, and

upon determining the value-existence structure contains the given value and the respective indicator indicates that the given value is stored in the given column, retrieving from the first data table the matching rows containing the given value in the given column, and transmitting the matching rows of the first data table to the database client.

16. The system of claim 15, 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.

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

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

18. The system of claim 15, 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.

19. The system of claim 15, wherein the first data table is stored on a first storage media, wherein a second data table is stored on a second storage media, wherein the first storage media has a lower access speed than the second storage media, and wherein the operation further comprises:

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

20. The system of claim 19, 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.

21. The system of claim 19, 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.