QUERY AND EXADATA SUPPORT FOR HYBRID COLUMNAR COMPRESSED DATA

• US 20150032763A1
• Filed: 09/08/2014
• Published: 01/29/2015
• Est. Priority Date: 11/12/2009
• 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, KULKARNI, SACHIN, GANESH, AMIT
• Granted Patent: US 10,025,820 B2
• Time in Patent Office: Days
• US Class Current: 707/754
• 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:

a storage system storing rows of database tables in a plurality of data blocks in non-volatile storage, each data block of said plurality of data blocks storing one or more columns of one or more rows of a database table of said database tables, said database tables being managed by a DBMS configured to return data from said database tables in response to database queries issued to the DBMS by clients of the DBMS;

wherein said storage system is configured to;

return, in entirety, requested data blocks to said DBMS in response to a request made by the DBMS that identifies the requested data blocks; and

return, to said DBMS, rows that satisfy one or more column criteria in response to a request made the DBMS that identifies data blocks to filter according to the one or more column criteria;

said storage system receiving a particular request for particular data blocks filtered according to particular one or more column criteria, said particular request identifying said particular data blocks and said one or more column criteria;

in response to receiving said particular request for the particular data blocks;

scanning said particular data blocks for particular rows having column values that satisfy said particular one or more criteria;

storing in a return buffer the particular rows having column values that satisfy said particular one or more criteria; and

returning the particular rows stored in said return buffer to said DBMS.

2. The method of claim 1, wherein:

said particular one or more column criteria is based on a set of one or more predicates from a particular query from a particular client to the DBMS;

each predicate of the one or more predicates identifies a column of the database table.

3. The method of claim 1 comprising:

retrieving data values only for a first column identified in a first criterion of said particular one or more criteria;

evaluating the first criterion based on data values in the first column;

storing, at a position within a first vector, a criteria satisfaction value corresponding to a row of the particular rows that indicates that a data value in the first column of the row that satisfies the first criterion.

4. The method of claim 3, wherein only rows having a corresponding criteria satisfaction value stored in the first vector are stored in the return buffer.

5. The method of claim 3, wherein the one or more criteria comprise a plurality of criteria, and the method further comprising:

determining, whether the criteria satisfaction value corresponding to any row is stored in the first 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 criteria;

storing, at a position within a second vector, a second criteria satisfaction value corresponding to a row of the particular rows that indicates that a data value in a second column of the row that satisfies the second criteria;

wherein the second criteria identifies the second column;

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

6. The method of claim 5, wherein only rows having a corresponding criteria satisfaction value stored in the cumulative vector are stored in the return buffer.

7. The method of claim 1, further comprising compressing the particular rows in the return buffer within one or more compression units.

8. The method of claim 7, wherein returning the particular rows stored in the said return buffer to the DBMS comprises returning said one or more compression units.

9. The method of claim 1, wherein:

columns in said particular data blocks are compressed independently;

a first criterion of the particular one or more column criteria only requires data values from a first column to be satisfied; and

scanning said particular data blocks for particular rows having column values that satisfy said particular one or more criteria comprises;

evaluating said first criterion that identifies said first column by performing a first decompression on only said first column, and evaluating said first criterion based on data values in said first column.

10. The method of claim 1, wherein:

each data block of said particular data blocks contains metadata associated with column values in the data block; and

scanning said particular data blocks comprises determining whether said particular rows have column values that satisfy said particular one or more criteria based on said metadata in each data block of said particular data blocks.

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

a storage system storing rows of database tables in a plurality of data blocks in non-volatile storage, each data block of said plurality of data blocks storing one or more columns of one or more rows of a database table of said database tables, said database tables being managed by a DBMS configured to return data from said database tables in response to database queries issued to the DBMS by clients of the DBMS;

wherein said storage system is configured to;

return, in entirety, requested data blocks to said DBMS in response to a request made by the DBMS that identifies the requested data blocks; and

return, to said DBMS, rows that satisfy one or more column criteria in response to a request made the DBMS that identifies data blocks to filter according to the one or more column criteria;

said storage system receiving a particular request for particular data blocks filtered according to particular one or more column criteria, said particular request identifying said particular data blocks and said one or more column criteria;

in response to receiving said particular request for the particular data blocks;

scanning said particular data blocks for particular rows having column values that satisfy said particular one or more criteria;

storing in a return buffer the particular rows having column values that satisfy said particular one or more criteria; and

returning the particular rows stored in said return buffer to said DBMS.

12. The computer-readable storage medium of claim 11, wherein:

said particular one or more column criteria is based on a set of one or more predicates from a particular query from a particular client to the DBMS;

each predicate of the one or more predicates identifies a column of the database table.

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

retrieving data values only for a first column identified in a first criterion of said particular one or more criteria;

evaluating the first criterion based on data values in the first column;

storing, at a position within a first vector, a criteria satisfaction value corresponding to a row of the particular rows that indicates that a data value in the first column of the row that satisfies the first criterion.

14. The computer-readable storage medium of claim 13, wherein only rows having a corresponding criteria satisfaction value stored in the first vector are stored in the return buffer.

15. The computer-readable storage medium of claim 13, wherein the one or more criteria comprise a plurality of criteria, and the instructions further include instructions for:

determining, whether the criteria satisfaction value corresponding to any row is stored in the first 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 criteria;

storing, at a position within a second vector, a second criteria satisfaction value corresponding to a row of the particular rows that indicates that a data value in a second column of the row that satisfies the second criteria;

wherein the second criteria identifies the second column;

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

16. The computer-readable storage medium of claim 15, wherein only rows having a corresponding criteria satisfaction value stored in the cumulative vector are stored in the return buffer.

17. The computer-readable storage medium of claim 11, wherein the instructions further include instructions for compressing the particular rows in the return buffer within one or more compression units.

18. The computer-readable storage medium of claim 17, wherein returning the particular rows stored in the said return buffer to the DBMS comprises returning said one or more compression units.

19. The computer-readable storage medium of claim 11, wherein:

columns in said particular data blocks are compressed independently;

a first criterion of the particular one or more column criteria only requires data values from a first column to be satisfied; and

scanning said particular data blocks for particular rows having column values that satisfy said particular one or more criteria comprises;

evaluating said first criterion that identifies said first column by performing a first decompression on only said first column, and evaluating said first criterion based on data values in said first column.

20. The computer-readable storage medium of claim 11, wherein:

each data block of said particular data blocks contains metadata associated with column values in the data block; and

scanning said particular data blocks comprises determining whether said particular rows have column values that satisfy said particular one or more criteria based on said metadata in each data block of said particular data blocks.