ON-LINE TRANSACTION PROCESSING (OLTP) COMPRESSION AND RE-COMPRESSION OF DATABASE DATA

• US 20130159246A1
• Filed: 02/09/2013
• Published: 06/20/2013
• Est. Priority Date: 10/19/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Marwah, Vineet, Pandey, Dheeraj, Ganesh, Amit, Stredie, Valentin G.
• Granted Patent: US 8,943,029 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 2212/401 Compressed data
• CPC Class Codes: H03M 7/30 Compression speech analysis-synthesis for redundancy reduction G10L19/00; for image communication H04N; Expansion; Suppression of unnecessary data, e.g. redundancy reduction

Abstract

A computer is programmed to compress data of a database in response to database modification language (DML) statements generated by on-line transaction processing (OLTP) systems. In several embodiments, data that is initially added to a database block is left uncompressed until a predetermined condition is satisfied, which happens infrequently (relative to OLTP transactions on the block). When satisfied, the computer automatically compresses all uncompressed data in the block, which increases the amount of unused space in the block. New data is thereafter added uncompressed to the partially compressed block, until satisfaction of a predetermined condition whereby the partially compressed block is again compressed, i.e. re-compressed. Adding of new data to a partially compressed block and its compression are repeated unless another predetermined condition is met, in response to which the block is not further re-compressed, thereby to recognize a limit on the benefit from compression.

Claims

1. A method implemented in a computer for compression of data in a database, the method comprising:

determining whether at least one predetermined condition is satisfied;

in response to the determining that the at least one predetermined condition is satisfied;

decompressing first compressed data from a block comprising the first compressed data and first uncompressed data, to obtain second uncompressed data from the first compressed data, the block being comprised among multiple blocks in the database; and

compressing at least the first uncompressed data and the second uncompressed data together, to generate second compressed data; and

storing the block in a mass storage device.

2. The method of claim 1 wherein:

unused space in the block after compressing is greater than unused space in the block prior to decompressing.

3. The method of claim 1 further comprising:

writing new uncompressed data to the block if at least another predetermined condition is satisfied, else writing the new uncompressed data to another block.

4. The method of claim 3 wherein:

the writing is performed subsequent to the compressing and prior to the storing.

5. The method of claim 3 wherein:

the writing is performed prior to the decompressing; and

the new uncompressed data is compressed during the compressing.

6. The method of claim 1 wherein:

the at least one predetermined condition comprises unused space within the block being below a threshold.

7. The method of claim 1 further comprising:

repeating, at least the decompressing and the compressing, until unused space in the block satisfies another predetermined condition.

8. The method of claim 1 wherein:

the block comprises a set of symbols; and

the second compressed data comprises a plurality of references, each reference identifying a symbol in the set of symbols.

9. The method of claim 8 wherein:

at least a first symbol in the set of symbols is at a level of columns in a database table and at least a second symbol in the set of symbols spans across a group of columns.

10. The method of claim 1 wherein:

the block is comprised in a segment in the database, and the segment comprises the multiple blocks.

11. The method of claim 1 wherein:

the block is of fixed size.

12. The method of claim 1 further comprising:

receiving a database modification language (DML) statement from an on-line transaction processing (OLTP) system, prior to the decompressing.

13. The method of claim 12 wherein:

the database modification language (DML) statement is comprised in a transaction; and

the at least one predetermined condition is satisfied on receipt of a commit statement of the transaction.

14. One or more non-transitory computer readable storage media comprising instructions to at least one processor to process data in a database, the instructions comprising:

instructions to check whether at least one predetermined condition is satisfied;

responsive to the at least one predetermined condition being determined to be satisfied by execution of the instructions to check;

instructions to decompress first compressed data from a block comprising the first compressed data and first uncompressed data, to obtain second uncompressed data from the first compressed data, the block being comprised among multiple blocks in the database; and

instructions to compress at least the first uncompressed data and the second uncompressed data together, to generate second compressed data; and

instructions to store the block in a mass storage device.

15. The one or more non-transitory computer readable storage media of claim 14 wherein:

unused space in the block after compressing is greater than unused space in the block prior to execution of the instructions to decompress.

16. The one or more non-transitory computer readable storage media of claim 14 further comprising:

instructions to write new uncompressed data to the block if at least another predetermined condition is satisfied, else write the new uncompressed data to another block.

17. The one or more non-transitory computer readable storage media of claim 16 wherein:

the instructions to write are to be executed subsequent to execution of the instructions to compress and prior to the instructions to store.

18. The one or more non-transitory computer readable storage media of claim 16 wherein:

the instructions to write are to be executed prior to execution of the instructions to decompress; and

the new uncompressed data is compressed by the instructions to compress.

19. The one or more non-transitory computer readable storage media of claim 14 wherein:

the at least one predetermined condition comprises unused space within the block being below a threshold.

20. The one or more non-transitory computer readable storage media of claim 14 further comprising:

instructions to repeatedly execute, at least the instructions to decompress and the instructions to compress, until unused space in the block satisfies another predetermined condition.

21. The one or more non-transitory computer readable storage media of claim 14 wherein:

the block comprises a set of symbols; and

the second compressed data comprises a plurality of references, each reference identifying a symbol in the set of symbols.

22. The one or more non-transitory computer readable storage media of claim 21 wherein:

at least a first symbol in the set of symbols is at a level of columns in a database table and at least a second symbol in the set of symbols spans across a group of columns.

23. The one or more non-transitory computer readable storage media of claim 14 wherein:

the block is comprised in a segment in the database, and the segment comprises the multiple blocks.

24. The one or more non-transitory computer readable storage media of claim 14 wherein:

the block is of fixed size.

25. The one or more non-transitory computer readable storage media of claim 14 further comprising:

instructions to receive a database modification language (DML) statement from an on-line transaction processing (OLTP) system, prior to execution of the instructions to decompress.

26. The one or more non-transitory computer readable storage media of claim 25 wherein:

the database modification language (DML) statement is comprised in a transaction; and

the at least one predetermined condition is satisfied on receipt of a commit statement of the transaction.

27. A database management system, implemented at least partially by a processor to compress data in a database, the database management system comprising:

means for checking whether at least one predetermined condition is satisfied;

responsive to said at least one predetermined condition being found to be satisfied by the means for checking;

means for decompressing first compressed data from a block comprising the first compressed data and first uncompressed data, to obtain second uncompressed data from the first compressed data, the block being comprised among multiple blocks in the database;

means for compressing at least the first uncompressed data and the second uncompressed data together, to generate second compressed data; and

means for storing the block in a mass storage device.

28. The database management system of claim 27 wherein:

the at least one predetermined condition comprises unused space within the block being below a threshold.

29. The database management system of claim 27 further comprising:

means for writing new uncompressed data to the block if at least another predetermined condition is satisfied, else writing the new uncompressed data to another block.

30. The database management system of claim 29 wherein:

the means for writing is to be operated subsequent to operation of the means for decompressing and prior to operation of the means for storing.

31. The database management system of claim 29 wherein:

the means for writing is to be operated prior to operation of the means for decompressing.

32. The database management system of claim 27 further comprising:

means for repeatedly operating, at least the means for decompressing and the means for compressing, until unused space in the block satisfies another predetermined condition.

33. The database management system of claim 27 wherein:

the block comprises a set of symbols; and

the second compressed data comprises a plurality of references, each reference identifying a symbol in the set of symbols.

34. The database management system of claim 27 wherein:

at least a first symbol in the set of symbols is at a level of columns in a database table and at least a second symbol in the set of symbols spans across a group of columns.

35. The database management system of claim 27 further comprising:

means for receiving a database modification language (DML) statement from an on-line transaction processing (OLTP) system, prior to operation of the means for decompressing.