Query and exadata support for hybrid columnar compressed data

• US 8,832,142 B2
• Filed: 08/30/2010
• Issued: 09/09/2014
• Est. Priority Date: 08/30/2010
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Marwah, Vineet, Kapoor, Vikram, Kamp, Jesse, Shergill, Kam, Macnicol, Roger, Kulkami, Sachin, Ganesh, Amit
• Primary Examiner(s): Perveen, Rehana
• Assistant Examiner(s): Khong, Alexander
• Application Number:
  US12/871,824
• Publication Number:
  US 20120054225A1
• Time in Patent Office: 1,471 Days
• US Class Current: 707/769
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/2457 with adaptation to user needs

Abstract

A method and apparatus is provided for optimizing queries received by a database system that relies on an intelligent data storage server to manage storage for the database system. Storing compression units in hybrid columnar format, the storage manager evaluates simple predicates and only returns data blocks containing rows that satisfy those predicates. The returned data blocks are not necessarily stored persistently on disk. That is, the storage manager is not limited to returning disc block images. The hybrid columnar format enables optimizations that provide better performance when processing typical database workloads including both fetching rows by identifier and performing table scans.

Claims

1. A method comprising:

for each set of rows of multiple sets of rows in a database table, a storage system storing on a non-volatile storage, said each set of rows in a separate compression unit of a plurality of compression units, wherein said each set of rows comprises columns of the database table that are stored in column major format within said separate compression unit, wherein said plurality of compression units are structures stored on the non-volatile storage for storing rows in said database table;

the storage system receiving a request from a database server to return compression units that include a column having a value that satisfies one or more criteria specified in the request;

in response to said request;

the storage system scanning the plurality of compression units for rows having column values that satisfy the one or more criteria;

the storage system returning one or more compression units, wherein the one or more compression units have a column value that satisfies the one or more criteria;

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

2. The method of claim 1, comprising:

wherein said one or more criteria is based on a set of one or more predicates from a database query, wherein each predicate of the set of the one or more predicates identifies a column of the database table;

retrieving data values only for a first column identified in a first predicate of said set of the one or more predicates, and evaluating the first predicate based on data values in the first column in one or more rows;

storing, at a position within a vector, a predicate satisfaction value corresponding to a row of the one or more rows that indicates that a data value in the first column of the row that satisfies the first predicate;

wherein the position within the vector corresponds to a row of the one or more rows.

3. The method of claim 2, wherein the database query includes a plurality of predicates, and the method further comprising:

determining, whether the predicate satisfaction value corresponding to any row is stored in the vector; and

in response to determining that no predicate satisfaction value is stored in the vector for any row, determining not to evaluate a second predicate.

4. The method of claim 2, wherein the vector is a cumulative vector, and the method further comprising:

determining, whether the predicate satisfaction value corresponding to any row is stored in the vector; and

in response to determining that at least one predicate satisfaction value is stored in the vector for any row, determining to evaluate a second predicate;

storing, at a position within a second vector, a second predicate satisfaction value corresponding to a row of the one or more rows that indicates that a data value in a second column of the row that satisfies the second predicate;

wherein the position within the second vector corresponds to a row of the one or more rows;

wherein the second predicate identifies the second column;

performing an operation on the second vector and the cumulative vector and storing results of the operation in the cumulative vector.

5. The method of claim 4, wherein only rows having a corresponding predicate satisfaction value stored in the cumulative vector are stored in the one or more compression units.

6. The method of claim 1, further comprising compressing data in rows persistently stored within a compression unit.

7. The method of claim 6, wherein each column is compressed independently from another column, of said columns.

8. The method of claim 7, wherein:

compressing the data further comprises performing a first level of compression on the data; and

after performing the first level of compression on the data, performing a second level of compression on the data.

9. The method of claim 8, wherein:

said one or more criteria includes a set of one or more predicates from a database query, wherein each predicate of the set of the one or more predicates identifies a column of the database table;

the method further comprising evaluating a first predicate that identifies a first column, wherein evaluating the first predicate only requires retrieving column data values by performing a first level of decompression on said first column, and evaluating the first predicate based on data values in the first column.

10. The method of claim 8, further comprising storing, within the compression unit, metadata associated with each column that indicates a range of values stored within the column.

11. The method of claim 10, further comprising evaluating a query that compares a column value in the query to the range of values based on the metadata associated with the column stored within the compression unit, and evaluating the query without performing first level decompression or second level decompression.

16. The method of claim 1, wherein only rows that include a column having a value that satisfies the one or more criteria specified in the request are stored in one of the one or more compression units.

33. The method of claim 1 comprising:

eliminating one or more rows from a compression unit that do not have a column value that satisfies the one or more criteria;

wherein the one or more compression units include at least one row from said compression unit but does not contain said one or more rows.

12. A method comprising:

in response to a request for one or more compression units stored in a data storage system;

a database system receiving at least one compression unit containing a plurality of rows, wherein at least a portion of said plurality of rows comprise columns, wherein at least one column of the columns is stored in column-major format;

rewriting data stored in each compression unit of said at least one compression unit into row-major format, wherein rewriting further comprises;

for each compression unit of said at least one compression unit;

determining a number of columns for said each compression unit;

determining a number of rows based on said number of columns and a processor cache size;

iteratively performing until all rows returned in said each compression unit are read;

 

reading said number of rows from said each compression unit stored in column-major order and storing said number of rows into a matrix;

 

reading said number of rows from said matrix and writing said number of rows into a return buffer in row-major order; and

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

13. The method of claim 12, wherein:

the size of the matrix is less than the size of the processor cache; and

once the matrix is stored in the processor cache, operations on the data stored within the matrix are guaranteed not to require any additional input/output (I/O) operations.

14. The method of claim 12, wherein the size of the matrix is one third of the size of the processor cache.

15. The method of claim 12, wherein:

the at least one compression unit is compressed and received from the data storage system with a vector;

the vector contains an entry corresponding to each row of the plurality of rows, and data in said each vector entry indicating which rows of the plurality of rows belong to a result set.

17. A computer-readable storage medium storing instructions which when executed by one or more processors cause performance of:

for each set of rows of multiple sets of rows in a database table, a storage system storing on a non-volatile storage, said each set of rows in a separate compression unit of a plurality of compression units, wherein said each set of rows comprises columns of the database table that are stored in column major format within said separate compression unit, wherein said plurality of compression units are structures stored on the non-volatile storage for storing rows in said database table;

the storage system receiving a request from a database server to return compression units that include a column having a value that satisfies one or more criteria specified in the request;

in response to said request;

the storage system scanning the plurality of compression units for rows having column values that satisfy the one or more criteria;

the storage system returning one or more compression units, wherein the one or more compression units have a column value that satisfies the one or more criteria.

18. The computer-readable storage medium of claim 17, wherein the instructions further include instructions for:

wherein said one or more criteria is based on a set of one or more predicates from a database query, wherein each predicate of the set of the one or more predicates identifies a column of the database table;

retrieving data values only for a first column identified in a first predicate of said set of the one or more predicates, and evaluating the first predicate based on data values in the first column in one or more rows;

storing, at a position within a vector, a predicate satisfaction value corresponding to a row of the one or more rows that indicates that a data value in the first column of the row that satisfies the first predicate;

wherein the position within the vector corresponds to a row of the one or more rows.

19. The computer-readable storage medium of claim 18, wherein the database query includes a plurality of predicates, and wherein the instructions further include instructions for:

determining, whether the predicate satisfaction value corresponding to any row is stored in the vector; and

in response to determining that no predicate satisfaction value is stored in the vector for any row, determining not to evaluate a second predicate.

20. The computer-readable storage medium of claim 18, wherein the vector is a cumulative vector, and wherein the instructions further include instructions for:

determining, whether the predicate satisfaction value corresponding to any row is stored in the vector; and

in response to determining that at least one predicate satisfaction value is stored in the vector for any row, determining to evaluate a second predicate;

storing, at a position within a second vector, a second predicate satisfaction value corresponding to a row of the one or more rows that indicates that a data value in a second column of the row that satisfies the second predicate;

wherein the position within the second vector corresponds to a row of the one or more rows;

wherein the second predicate identifies the second column;

performing an operation on the second vector and the cumulative vector and storing results of the operation in the cumulative vector.

21. The computer-readable storage medium of claim 20, wherein only rows having a corresponding predicate satisfaction value stored in the cumulative vector are stored in the one or more compression units.

22. The computer-readable storage medium of claim 17, wherein the instructions further include instructions for compressing data in rows persistently stored within a compression unit.

23. The computer-readable storage medium of claim 22, wherein each column is compressed independently from another column, of said columns.

24. The computer-readable storage medium of claim 23, wherein:

compressing the data further comprises performing a first level of compression on the data; and

after performing the first level of compression on the data, performing a second level of compression on the data.

25. The computer-readable storage medium of claim 24, wherein:

said one or more criteria includes a set of one or more predicates from a database query, wherein each predicate of the set of the one or more predicates identifies a column of the database table;

the instructions further include instructions for evaluating a first predicate that identifies a first column, wherein evaluating the first predicate only requires retrieving column data values by performing a first level of decompression on said first column, and evaluating the first predicate based on data values in the first column.

26. The computer-readable storage medium of claim 24, wherein the instructions further include instructions for storing, within the compression unit, metadata associated with each column that indicates a range of values stored within the column.

27. The computer-readable storage medium of claim 26, wherein the instructions further include instructions for evaluating a query that compares a column value in the query to the range of values based on the metadata associated with the column stored within the compression unit, and evaluating the query without performing first level decompression or second level decompression.

28. The computer-readable storage medium of claim 17, wherein only rows that include a column having a value that satisfies the one or more criteria specified in the request are stored in one of the one or more compression units.

34. The computer-readable storage medium of claim 17 wherein the instructions further include instructions which when executed by cause performance of:

eliminating one or more rows from a compression unit that do not have a column value that satisfies the one or more criteria;

wherein the one or more compression units include at least one row from said compression unit but does not contain said one or more rows.

29. A computer-readable storage medium storing instructions which, when executed, cause one or more processors to perform the steps of:

in response to a request for one or more compression units stored in a data storage system;

a database system receiving at least one compression unit containing a plurality of rows, wherein at least a portion of said plurality of rows comprise columns, wherein at least one column of the columns is stored in column-major format;

rewriting data stored in each compression unit of said at least one compression unit into row-major format, wherein rewriting further comprises;

for each compression unit of said at least one compression unit;

determining a number of columns for said each compression unit;

determining a number of rows based on said number of columns and a processor cache size;

iteratively performing until all rows returned in said each compression unit are read;

 

reading said number of rows from said each compression unit stored in column-major order and storing said number of rows into a matrix;

 

reading said number of rows from said matrix and writing said number of rows into a return buffer in row-major order.

30. The computer-readable storage medium of claim 29, wherein:

the size of the matrix is less than the size of the processor cache; and

once the matrix is stored in the processor cache, operations on the data stored within the matrix are guaranteed not to require any additional input/output (I/O) operations.

31. The computer-readable storage medium of claim 29, wherein the size of the matrix is one third of the size of the processor cache.

32. The computer-readable storage medium of claim 29, wherein:

the at least one compression unit is compressed and received from the data storage system with a vector;

the vector contains an entry corresponding to each row of the plurality of rows, and data in said each vector entry indicating which rows of the plurality of rows belong to a result set.

35. A system comprising:

a database server; and

a storage system, comprising one or more non-volatile storage devices, configured to;

for each set of rows of multiple sets of rows in a database table, store said each set of rows in a separate compression unit of a plurality of compression units, wherein said each set of rows comprises columns of the database table that are stored in column major format within said separate compression unit, wherein said plurality of compression units are structures stored on the storage system for storing rows in said database table;

receive a request from the database server to return compression units that include a column having a value that satisfies one or more criteria specified in the request;

in response to said request;

scan the plurality of compression units for rows having column values that satisfy the one or more criteria;

return one or more compression units, wherein the one or more compression units have a column value that satisfies the one or more criteria.

36. The system of claim 35, wherein:

said one or more criteria is based on a set of one or more predicates from a database query, wherein each predicate of the set of the one or more predicates identifies a column of the database table;

the storage system is further configured to retrieve data values only for a first column identified in a first predicate of said set of the one or more predicates, and evaluate the first predicate based on data values in the first column in one or more rows;

store, at a position within a vector, a predicate satisfaction value corresponding to a row of the one or more rows that indicates that a data value in the first column of the row that satisfies the first predicate;

the position within the vector corresponds to a row of the one or more rows.

37. The system of claim 36, wherein:

the database query includes a plurality of predicates; and

the storage system is further configured to;

determine whether the predicate satisfaction value corresponding to any row is stored in the vector; and

in response to determining that no predicate satisfaction value is stored in the vector for any row, determine not to evaluate a second predicate.

38. The system of claim 36, wherein:

the vector is a cumulative vector; and

the storage system is further configured to;

determine whether the predicate satisfaction value corresponding to any row is stored in the vector; and

in response to determining that at least one predicate satisfaction value is stored in the vector for any row, determine to evaluate a second predicate;

store, at a position within a second vector, a second predicate satisfaction value corresponding to a row of the one or more rows that indicates that a data value in a second column of the row that satisfies the second predicate;

wherein the position within the second vector corresponds to a row of the one or more rows;

wherein the second predicate identifies the second column;

perform an operation on the second vector and the cumulative vector and storing results of the operation in the cumulative vector.

39. The system of claim 38, wherein only rows having a corresponding predicate satisfaction value stored in the cumulative vector are stored in the one or more compression units.

40. The system of claim 35, wherein the storage system is further configured to compress data in rows persistently stored within a compression unit.

41. The system of claim 40, wherein each column is compressed independently from another column, of said columns.

42. The system of claim 41, wherein the storage system is further configured to:

compress the data further comprises performing a first level of compression on the data; and

after performing the first level of compression on the data, perform a second level of compression on the data.

43. The system of claim 42, wherein:

said one or more criteria includes a set of one or more predicates from a database query, wherein each predicate of the set of the one or more predicates identifies a column of the database table;

the storage device is further configured to evaluate a first predicate that identifies a first column, wherein evaluating the first predicate only requires retrieving column data values by performing a first level of decompression on said first column, and evaluating the first predicate based on data values in the first column.

44. The system of claim 42, wherein the storage system is further configured to store, within the compression unit, metadata associated with each column that indicates a range of values stored within the column.

45. The system of claim 44, wherein the storage system is further configured to evaluate a query that compares a column value in the query to the range of values based on the metadata associated with the column stored within the compression unit, and evaluate the query without performing first level decompression or second level decompression.

46. The system of claim 35, wherein only rows that include a column having a value that satisfies the one or more criteria specified in the request are stored in one of the one or more compression units.

47. The system of claim 35, wherein the storage system is further configured to:

eliminate one or more rows from a compression unit that do not have a column value that satisfies the one or more criteria;

wherein the one or more compression units include at least one row from said compression unit but does not contain said one or more rows.

48. A system comprising:

a data storage system, comprising one or more non-volatile storage devices;

a database system configured to, in response to a request for one or more compression units stored in the data storage system;

receive at least one compression unit containing a plurality of rows, wherein at least a portion of said plurality of rows comprises columns, wherein at least one column of the columns is stored in column-major format;

rewrite data stored in each compression unit of said at least one compression unit into row-major format, wherein rewriting further comprises;

for each compression unit of said at least one compression unit;

determining a number of columns for said each compression unit;

determining a number of rows based on said number of columns and a processor cache size;

iteratively performing until all rows returned in said each compression unit are read;

reading said number of rows from said each compression unit stored in column-major order and storing said number of rows into a matrix;

reading said number of rows from said matrix and writing said number of rows into a return buffer in row- major order.

49. The system of claim 48, wherein:

the size of the matrix is less than the size of the processor cache; and

once the matrix is stored in the processor cache, operations on the data stored within the matrix are guaranteed not to require any additional input/output (I/O) operations.

50. The system of claim 48, wherein the size of the matrix is one third of the size of the processor cache.

51. The system of claim 48, wherein:

the at least one compression unit is compressed and received from the data storage system with a vector;

the vector contains an entry corresponding to each row of the plurality of rows, and data in said each vector entry indicating which rows of the plurality of rows belong to a result set.