Compressing data stored in an intermediate or result table of a database

• US 7,383,270 B1
• Filed: 02/03/2004
• Issued: 06/03/2008
• Est. Priority Date: 02/03/2004
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Kostamaa, O. Pekka, Morris, J. Mark
• Primary Examiner(s): Alam; Hosain
• Assistant Examiner(s): Ahn; Sangwoo
• Application Number:
  US10/770,796
• Time in Patent Office: 1,582 Days
• Field of Search: 707 1- 10, 707100-1041
• US Class Current: 707/693
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• 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
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A database system receives a transaction specifying one or more conditions to apply to a first table, and inserts data selected in the transaction into a second. The database system further generates a value list used for compressing a column of the second table, where generating the value list comprises one of inheriting entries of the value list from another value list associated with the first table, and deriving the value list based on the one or more conditions to apply on the first table.

Claims

1. A method for use in a database system, comprising:

performing value list compression, using a first value list, of a column of a first table based on frequently occurring values of the column, wherein the first value list contains the frequently occurring values, wherein performing compression of the column of the first table causes encoded values to be stored in the first table instead of data values of the column of the first table;

inserting data into a second table as a result of a database operation involving the first table;

performing value list compression, using a second value list, of a column of the second table by inheriting the frequently occurring values of the column of the first table, wherein the second value list contains at least a subset of the first value list, wherein performing compression of the column of the second table causes encoded values to be stored in the second table instead of data values of the column of the second table, wherein the database operation specifies one or more conditions to apply to the column of the first table; and

generating the second value list from the first value list based on the one or more conditions to apply to the column of the first table.

2. The method of claim 1, wherein generating the second value list comprises applying a predicate specified in a query for the database operation on the first value list.

3. The method of claim 2, wherein generating the second value list further comprises applying an operator specified in the query for the database operation on the first value list.

4. The method of claim 1, wherein generating the second value list comprises applying a column-level constraint to the first value list.

5. The method of claim 1, wherein generating the second value list comprises applying a join condition to the first value list.

6. The method of claim 1, wherein generating the second value list comprises applying a condition specified in a conditional statement associated with the database operation.

7. The method of claim 1, further comprising projecting selected data values of the column in the first table into the column in the second table in the database operation.

8. An article comprising at least one storage medium containing instructions that when executed cause a database system to:

perform compression, using a first value list, of a column of a first table based on frequently occurring values of the column of the first table, wherein the first value list contains the frequently occurring values, wherein performing compression of the first table causes encoded values to be stored in the first table instead of data values of the column of the first table;

insert data into a second table as a result of a database operation involving the first table;

perform compression, using a second value list, of a column of the second table, based on inheritance of the frequently occurring values of the column of the first table, wherein the second value list contains at least a subset of the first value list, wherein performing compression of the second table causes encoded values to be stored in the second table instead of data values of the column of the second table, wherein the database operation specifies one or more conditions to apply to the column of the first table; and

generate the second value list from the first value list based on the one or more conditions to apply to the column of the first table.

9. The article of claim 8, wherein generating the second value list comprises applying at least one of a predicate and an operator specified in a query for the database operation on the first value list.

10. The article of claim 9, wherein generating the second value list further comprises applying a column-level constraint to the first value list.

11. The article of claim 8, wherein generating the second value list comprises applying a join condition to the first value list.

12. The article of claim 11, wherein applying the join condition comprises applying an equijoin condition.

13. The article of claim 12, wherein the database operation comprises a join operation to join at least a first parent table and a second parent table, the first parent table associated with the first value list, and the second parent table associated with a third value list for compressing a column of the second parent table,

wherein generating the second value list comprises performing an equijoin of the first and third value lists.

14. The article of claim 11, wherein applying the join condition comprises applying a non-equality join condition.

15. A database system comprising:

storage to store a first table and a second table; and

a controller to;

perform compression of a column of the first table using first specifications, wherein performing compression of the column of the first table causes encoded values to be stored in the first table instead of data values of the column of the first table;

receive a transaction specifying one or more conditions to apply on the first table;

insert data into the second table as a result of the transaction involving the first table;

perform compression of the second table using second compression specifications derived based on the one or more conditions specified in the transaction, wherein the compression comprises value list compression of a column of the second table that is based on a value list containing values of the column of the second table, wherein the second compression specifications comprise the value list,

wherein the one or more conditions comprise a predicate enumeration that includes an enumerated list of column values, and where the value list comprises the enumerated list of values.

16. A database system comprising:

storage to store a first table and a second table; and

a controller to;

perform compression of a column of the first table using first specifications, wherein performing compression of the column of the first table causes encoded values to be stored in the first table instead of data values of the colunn of the first table;

receive a transaction specifying one or more conditions to apply on the first table;

insert data into the second table as a result of the transaction involving the first table;

perform compression of the second table using second compression specifications derived based on the one or more conditions specified in the transaction, wherein the compression comprises value list compression of a column of the second table that is based on a value list containing values of the column of the second table, wherein the second compression specifications comprise the value list,

wherein the one or more conditions comprise a predicate range condition, and wherein the controller is adapted to generate an enumerated list of column values from the predicate range condition, the value list comprising the enumerated list.

17. A database system comprising:

storage to store a first table and a second table; and

a controller to;

perform compression of a column of the first table using first specifications, wherein performing compression of the column of the first table causes encoded values to be stored in the first table instead of data values of the column of the first table;

receive a transaction specifying one or more conditions to apply on the first table;

insert data into the second table as a result of the transaction involving the first table;

perform compression of the second table using second compression specifications derived based on the one or more conditions specified in the transaction, wherein the compression comprises value list compression of a column of the second table that is based on a value list containing values of the column of the second table, wherein the second compression specifications comprise the value list,

wherein the one or more conditions comprise a predicate expression, and wherein the controller is adapted to generate an enumerated list of column values based on the predicate expression, the value list comprising the enumerated list.

18. The database system of claim 17, wherein the controller comprises a database engine having plural access modules to concurrently access plural respective storage modules in the storage.

19. The database system of claim 18, wherein the access modules are adapted to perform compression of the first table in parallel, and to perform compression of the second table in parallel.