Techniques For Automatic Data Placement With Compression And Columnar Storage

• US 20120296883A1
• Filed: 05/18/2012
• Published: 11/22/2012
• Est. Priority Date: 08/30/2010
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ganesh, Amit, Marwah, Vineet, Kapoor, Vikram
• Granted Patent: US 8,959,057 B2
• Time in Patent Office: Days
• US Class Current: 707/693
• CPC Class Codes: G06F 16/185 Hierarchical storage management [HSM] systems, e.g. file migration or policies thereof details of archiving G06F16/11
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 3/0608 Saving storage space on storage systems
• CPC Class Codes: G06F 3/064 Management of blocks
• CPC Class Codes: G06F 3/0653 Monitoring storage devices or systems
• CPC Class Codes: G06F 3/0673 Single storage device

Abstract

For automatic data placement of database data, a plurality of access-tracking data is maintained. The plurality of access-tracking data respectively corresponds to a plurality of data rows that are managed by a database server. While the database server is executing normally, it is automatically determined whether a data row, which is stored in first one or more data blocks, has been recently accessed based on the access-tracking data that corresponds to that data row. After determining that the data row has been recently accessed, the data row is automatically moved from the first one or more data blocks to one or more hot data blocks that are designated for storing those data rows, from the plurality of data rows, that have been recently accessed.

Claims

1. A computer-implemented method comprising:

maintaining a plurality of access-tracking data that respectively corresponds to a plurality of data rows that are managed by a database server;

while the database server is executing in normal operation mode, performing steps comprising;

automatically determining whether a first data row, of the plurality of data rows, has been recently accessed based on first access-tracking data, of the plurality of access-tracking data, that corresponds to the first data row;

wherein the first data row is stored in first one or more data blocks;

after determining that the first data row has been recently accessed, automatically removing the first data row from the first one or more data blocks and storing the first data row in one or more hot data blocks;

wherein the one or more hot data blocks are designated for storing those data rows, from the plurality of data rows, that have been recently accessed;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein while the database server is executing, further performing the steps of:

automatically determining whether a second data row, of the plurality of data rows, has been recently accessed based on second access-tracking data, of the plurality of access-tracking data, that corresponds to the second data row;

wherein the second data row is stored in second one or more data blocks;

after determining that the second data row has not been recently accessed, automatically removing the second data row from the second one or more data blocks and storing the second data row in one or more cold data blocks;

wherein the one or more cold data blocks are designated for storing those data rows, from the plurality of data rows, that have not been recently accessed.

3. The method of claim 2, wherein while the database server is executing, further performing the step of compressing the one or more cold data blocks by using one or more compression mechanisms.

4. The method of claim 1, further comprising:

determining that a new data row is received by the database server; and

in response to receiving the new data row, causing the new data row to be stored in the one or more hot data blocks.

5. The method of claim 1, wherein:

the first one or more data blocks and the one or more hot data blocks are persistently stored on one or more storage devices; and

the method further comprises copying each of the one or more hot data blocks as an entire block from the one or more storage devices into memory cache managed by the database server.

6. The method of claim 1, wherein the plurality of data rows belong to one or more tables in one or more databases that are managed by the database server.

7. The method of claim 1, wherein for each particular data row, of the plurality of data rows, the plurality of access-tracking data includes a corresponding access-tracking data that comprises one or more of:

a first time value indicating the last time when the particular data row was read;

a second time value indicating the last time when the particular data row was modified;

a third time value indicating the last time when a table that stores the particular data row was read;

a fourth time value indicating the last time when the table that stores the particular data row was modified.

8. The method of claim 1, wherein maintaining the plurality of access-tracking data comprises:

continuously updating and storing a particular access-tracking data corresponding to a particular data row, of the plurality of data rows, in one or more of;

the particular data row; and

a block header of a particular data block in which the particular data row or a portion thereof is stored.

9. The method of claim 1, wherein:

the one or more hot data blocks are included in a plurality of hot data blocks, wherein the plurality of hot data blocks are organized in a hierarchy based on access times; and

the method further comprises selecting the one or more hot data blocks from the plurality of hot data blocks based at least on the first access-tracking data while the database server is executing.

10. The method of claim 1, wherein automatically removing the first data row from the first one or more data blocks and storing the first data row in the one or more hot data blocks is performed without adversely impacting on-going operations and transactions that are being executed by the database server.

11. The method of claim 1, wherein automatically removing the first data row from the first one or more data blocks and storing the first data row in the one or more hot data blocks is performed in accordance with one or more policies that are configured at the database server.

12. A computer-implemented method comprising:

maintaining a plurality of access-tracking data that respectively corresponds to a plurality of data rows that are managed by a database server;

while the database server is executing in normal operation mode, performing steps comprising;

automatically determining whether a first data row, of the plurality of data rows, has been recently accessed based on first access-tracking data, of the plurality of access-tracking data, that corresponds to the first data row;

wherein the first data row is stored on a first storage device;

after determining that the first data row has been recently accessed, automatically removing the first data row from the first storage device and storing the first data row on a second storage device;

wherein the second storage device is different from the first storage device;

wherein the method is performed by one or more computing devices.

13. The method of claim 12, wherein the second storage device is a high-performance storage device.

14. The method of claim 12, wherein while the database server is executing, further performing the steps of:

automatically determining whether a second data row, of the plurality of data rows, has been recently accessed based on second access-tracking data, of the plurality of access-tracking data, that corresponds to the second data row;

wherein the second data row is stored on a third storage device;

after determining that the second data row has not been recently accessed, automatically removing the second data row from the third storage device and storing the second data row on a high-capacity storage device that is designated for storing those data rows, from the plurality of data rows, that have not been recently accessed.

15. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, cause the one or more processors to perform steps comprising:

maintaining a plurality of access-tracking data that respectively corresponds to a plurality of data rows that are managed by a database server;

while the database server is executing in normal operation mode, performing steps comprising;

automatically determining whether a first data row, of the plurality of data rows, has been recently accessed based on first access-tracking data, of the plurality of access-tracking data, that corresponds to the first data row;

wherein the first data row is stored in first one or more data blocks;

after determining that the first data row has been recently accessed, automatically removing the first data row from the first one or more data blocks and storing the first data row in one or more hot data blocks;

wherein the one or more hot data blocks are designated for storing those data rows, from the plurality of data rows, that have been recently accessed.

16. The non-transitory computer-readable storage medium of claim 15, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors while the database server is executing, cause the one or more processors to perform:

automatically determining whether a second data row, of the plurality of data rows, has been recently accessed based on second access-tracking data, of the plurality of access-tracking data, that corresponds to the second data row;

wherein the second data row is stored in second one or more data blocks;

after determining that the second data row has not been recently accessed, automatically removing the second data row from the second one or more data blocks and storing the second data row in one or more cold data blocks;

wherein the one or more cold data blocks are designated for storing those data rows, from the plurality of data rows, that have not been recently accessed.

17. The non-transitory computer-readable storage medium of claim 16, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors while the database server is executing, cause the one or more processors to perform compressing the one or more cold data blocks by using one or more compression mechanisms.

18. The non-transitory computer-readable storage medium of claim 15, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform:

determining that a new data row is received by the database server; and

in response to receiving the new data row, causing the new data row to be stored in the one or more hot data blocks.

19. The non-transitory computer-readable storage medium of claim 15, wherein:

the first one or more data blocks and the one or more hot data blocks are persistently stored on one or more storage devices; and

the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform copying each of the one or more hot data blocks as an entire block from the one or more storage devices into memory cache managed by the database server.

20. The non-transitory computer-readable storage medium of claim 15, wherein the plurality of data rows belong to one or more tables in one or more databases that are managed by the database server.

21. The non-transitory computer-readable storage medium of claim 15, wherein for each particular data row, of the plurality of data rows, the plurality of access-tracking data includes a corresponding access-tracking data that comprises one or more of:

a first time value indicating the last time when the particular data row was read;

a second time value indicating the last time when the particular data row was modified;

a third time value indicating the last time when a table that stores the particular data row was read;

a fourth time value indicating the last time when the table that stores the particular data row was modified.

22. The non-transitory computer-readable storage medium of claim 15, wherein the instructions that cause maintaining the plurality of access-tracking data comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform:

continuously updating and storing a particular access-tracking data corresponding to a particular data row, of the plurality of data rows, in one or more of;

the particular data row; and

a block header of a particular data block in which the particular data row or a portion thereof is stored.

23. The non-transitory computer-readable storage medium of claim 15, wherein:

the one or more hot data blocks are included in a plurality of hot data blocks, wherein the plurality of hot data blocks are organized in a hierarchy based on access times; and

the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform selecting the one or more hot data blocks from the plurality of hot data blocks based at least on the first access-tracking data while the database server is executing.

24. The non-transitory computer-readable storage medium of claim 15, wherein the instructions that cause automatically removing the first data row from the first one or more data blocks and storing the first data row in the one or more hot data blocks are performed by the one or more processors without adversely impacting on-going operations and transactions that are being executed by the database server.

25. The non-transitory computer-readable storage medium of claim 15, wherein the instructions that cause automatically removing the first data row from the first one or more data blocks and storing the first data row in the one or more hot data blocks are performed by the one or more processors in accordance with one or more policies that are configured at the database server.

26. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, cause the one or more processors to perform steps comprising:

maintaining a plurality of access-tracking data that respectively corresponds to a plurality of data rows that are managed by a database server;

while the database server is executing in normal operation mode, performing steps comprising;

automatically determining whether a first data row, of the plurality of data rows, has been recently accessed based on first access-tracking data, of the plurality of access-tracking data, that corresponds to the first data row;

wherein the first data row is stored on a first storage device;

after determining that the first data row has been recently accessed, automatically removing the first data row from the first storage device and storing the first data row on a second storage device;

wherein the second storage device is different from the first storage device.

27. The non-transitory computer-readable storage medium of claim 26, wherein the second storage device is a high-performance storage device.

28. The non-transitory computer-readable storage medium of claim 26, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors while the database server is executing, cause the one or more processors to perform:

automatically determining whether a second data row, of the plurality of data rows, has been recently accessed based on second access-tracking data, of the plurality of access-tracking data, that corresponds to the second data row;

wherein the second data row is stored on a third storage device;

after determining that the second data row has not been recently accessed, automatically removing the second data row from the third storage device and storing the second data row on a high-capacity storage device that is designated for storing those data rows, from the plurality of data rows, that have not been recently accessed.