Query and exadata support for hybrid columnar compressed data

• US 10,025,820 B2
• Filed: 09/08/2014
• Issued: 07/17/2018
• 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
• Primary Examiner(s): Khong, Alexander
• Application Number:
  US14/480,009
• Publication Number:
  US 20150032763A1
• Time in Patent Office: 1,408 Days
• 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 comprising a processor and memory 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 database management system (“

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 via a network by the DBMS, wherein the request identifies the requested data blocks;

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

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

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

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

said storage system returning the particular rows stored in said return buffer to said DBMS;

wherein scanning said particular data blocks for particular rows having column values that satisfy said particular one or more column criteria includes, for a particular subset of said particular data blocks;

retrieving data values only for a first column identified in a first criterion of said particular one or more column 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;

determining to evaluate a second criterion;

storing, at a position within a second vector, a second criterion 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 criterion;

wherein the second criterion 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.

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, wherein only rows having a corresponding criteria satisfaction value stored in the first vector are stored in the return buffer.

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

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

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

7. 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 column criteria comprises;

evaluating said first criterion that identifies said first column by at least performing a first decompression on said first column and evaluating said first criterion based on data values in said first column;

after evaluating said second criterion, performing a second decompression on said second column and evaluating said second criterion based on data values in said second column.

8. The method of claim 1, wherein:

each data block of said particular data blocks is associated with metadata based on 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 column criteria based on said metadata associated with each data block of said particular data blocks.

17. The method of claim 1, wherein the particular rows comprise a first row from a first data block in said plurality of data blocks and a second row for a different second data block in said plurality of data blocks.

19. The method of claim 1, wherein a particular compression unit comprises compressed data from a plurality of data blocks.

20. The method of claim 19:

wherein said particular compression unit comprises a plurality of compression unit portions, a first compression unit portion comprising data from a first data block and a second compression unit portion comprising data from a second data block;

wherein the storage system scanning said particular data blocks for particular rows having column values that satisfy said particular one or more column criteria comprises determining that the first compression unit portion comprises one or more of the particular rows, but the second compression unit portion does not comprise one or more of the particular rows;

wherein said storage system storing the particular rows in the return buffer comprises decompressing the first compression unit portion and returning the one or more of the particular rows from the decompressed first compression unit portion.

21. The method of claim 19, further comprising:

storing compression unit metadata identifying locations at which compressed data for certain columns or rows is held in the compression units;

wherein said storage system storing the particular rows in the return buffer comprises using the compression unit metadata, determining locations in the compression units of the particular rows and decompressing only the rows in the determined locations.

22. The method of claim 19, further comprising:

storing compression unit metadata identifying, for the particular compression unit, a range of values for one or more columns in the particular compression unit;

wherein scanning said particular data blocks for the particular rows comprises determining whether the range of values in the compression unit metadata comprises the column values that satisfy said particular one or more column criteria;

decompressing the particular compression unit for further scanning in response to determining that the range of values in the compression unit metadata comprises the column values that satisfy said particular one or more column criteria.

23. The method of claim 1, further comprising:

determining, whether the criteria satisfaction value corresponding to any row is stored in the first vector;

in response to determining that at least one criteria satisfaction value corresponding to any row is stored in the first vector, determining to evaluate the second criterion.

24. The method of claim 1, said storage system storing in a return buffer the particular rows having column values that satisfy said particular one or more column criteria comprises said storage system storing in the return buffer only columns of the particular rows that satisfy the one or more column criteria.

9. A non-transitory computer-readable storage medium storing instructions which when executed by one or more processors cause:

a storage system comprising a processor and memory 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 database management system (“

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 via a network by the DBMS, wherein the request identifies the requested data blocks;

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

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

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

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

said storage system returning the particular rows stored in said return buffer to said DBMS;

wherein scanning said particular data blocks for particular rows having column values that satisfy said particular one or more column criteria includes, for a particular subset of said particular data blocks;

retrieving data values only for a first column identified in a first criterion of said particular one or more column 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;

determining to evaluate a second criterion;

storing, at a position within a second vector, a second criterion 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 criterion;

wherein the second criterion 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.

10. The non-transitory computer-readable storage medium of claim 9, 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.

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

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

13. The non-transitory computer-readable storage medium of claim 9, wherein the instructions, when executed by the one or more processors, further cause compressing the particular rows in the return buffer within one or more compression units.

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

15. The non-transitory computer-readable storage medium of claim 9, 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 column 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.

16. The non-transitory computer-readable storage medium of claim 9, wherein:

each data block of said particular data blocks is associated with metadata based on 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 column criteria based on said metadata associated with each data block of said particular data blocks.

18. The non-transitory computer-readable storage medium of claim 9, wherein the particular rows comprise a first row from a first data block in said plurality of data blocks and a second row for a different second data block in said plurality of data blocks.