Zero copy optimization for select * queries

Patent metadata

Field	Value
Publication number	US-11609909-B2
Application number	US-202117315281-A
Country	US
Kind code	B2
Filing date	May 8, 2021
Priority date	May 12, 2020
Publication date	Mar 21, 2023
Grant date	Mar 21, 2023

Abstract

A computer-implemented method includes receiving a query specifying an operation to perform on a first table of a plurality of data blocks stored. Each data block in the first table includes a respective reference count indicating a number of tables referencing the data block. The method also includes determining that the operation specified by the query includes copying the plurality of data blocks in the first table into a second table and, in response, for each data block of the plurality of data blocks in the first table copied into the second table, incrementing, the respective reference count associated with the data block in the first table, appending, by the data processing hardware, into metadata of the second table, a reference of the corresponding data block copied into the second table.

First claim

What is claimed is:

1. A computer-implemented method when executed on data processing hardware causes the data processing hardware to perform operations comprising:
receiving a query specifying an operation to perform on a first table of a plurality of data blocks stored on memory hardware in communication with the data processing hardware, each data block in the first table comprising a respective reference count indicating a number of tables referencing the data block;
determining that the operation specified by the query comprises copying the plurality of data blocks in the first table into a second table and in parallel determine that the query is a candidate for optimization, the optimization specifying a shallow copy for the query executor and neither duplicates any data blocks on disk nor consumes any CPU in decoding or encoding rows; and
in response to determining that the operation specified by the query comprises copying the plurality of data blocks in the first table into the second table, for each respective data block of the plurality of data blocks in the first table to be copied into the second table, wherein copying the plurality of data blocks comprises determining sub-operations of the query including a sub-operation for writing at least one data block to the memory hardware and a sub-operation for reading at least one data block from the memory hardware;
incrementing the respective reference count associated with the respective data block in the first table; and
appending, into metadata of the second table, a reference of the respective data block and the query optimizer stores pointers to a metadata of an original table.

2. The method of claim 1 , wherein copying the plurality of data blocks in the first table into the second table comprises copying each data block in the first table into the second table without duplicating any of the plurality of data blocks.

3. The method of claim 1 , wherein the query comprises a SELECT*Structured Query Language (SQL) statement.

4. The method of claim 1 , wherein determining that the operation specified by the query comprises copying the plurality of data blocks in the first table into the second table comprises algebrization of the query.

5. The method of claim 1 , wherein the sub-operation for writing at least one data block to the memory hardware comprises a materialize sub-operation.

6. The method of claim 1 , wherein the sub-operation for reading at least one data block from the memory hardware comprises a scan sub-operation.

7. The method of claim 1 , wherein determining that the determined sub-operations of the query comprise the scan sub-operation for reading the at least one data block comprises determining that the determined sub-operations of the query comprise only a single scan sub-operation for reading the at least one data block.

8. The method of claim 1 , wherein determining that the query comprises copying the plurality of the data blocks in the first table into the second table comprises:
determining an order of a list of columns associated with the query;
determining an order of columns of the first table; and determining that the order of the list of columns associated with the query is the same as the order of columns of the first table.

9. The method of claim 1 , wherein the operations further comprise, while determining that the query comprises copying the plurality of data blocks in the first table into the second table, initiating execution of the operation specified by the query over the first table.

10. The method of claim 9 , wherein the operations further comprise, in response to determining that the query comprises copying the plurality of data blocks in the first table into the second table halting execution of the query over the first table.

11. The method of claim 1 , wherein the operations further comprise, after appending the reference of each data block of the plurality of the data blocks into the metadata of the second table:
receiving a request to delete the first table; and
decrementing the reference count of each data block of the first table.

12. A system comprising:
data processing hardware; and
memory hardware in communication with the data processing hardware, the memory hardware storing instructions that when executed on the data processing hardware cause the data processing hardware to perform operations comprising:
receiving a query specifying an operation to perform on a first table of a plurality of data blocks stored on memory hardware in communication with the data processing hardware, each data block in the first table comprising a respective reference count indicating a number of tables referencing the data block;
determining that the operation specified by the query comprises copying the plurality of data blocks in the first table into a second table and in parallel determine that the query is a candidate for optimization, the optimization specifying a shallow copy for the query executor and neither duplicates any data blocks on disk nor consumes any CPU in decoding or encoding rows; and
in response to determining that the operation specified by the query comprises copying the plurality of data blocks in the first table into the second table, for each respective data block of the plurality of data blocks in the first table to be copied into the second table, wherein copying the plurality of data blocks comprises determining sub-operations of the query including a sub-operation for writing at least one data block to the memory hardware and a sub-operation for reading at least one data block from the memory hardware:
incrementing the respective reference count associated with the respective data block in the first table; and
appending into metadata of the second table, a reference of the respective data block and the query optimizer stores pointers to a metadata of an original table.

13. The system of claim 12 , wherein copying the plurality of data blocks in the first table into the second table comprises copying each data block in the first table into the second table without duplicating any of the plurality of data blocks.

14. The system of claim 12 , wherein the query comprises a SELECT*Structured Query Language (SQL) statement.

15. The system of claim 12 , wherein determining that the operation specified by the query comprises copying the plurality of data blocks in the first table into the second table comprises algebrization of the query.

16. The system of claim 12 , wherein the sub-operation for writing at least one data block to the memory hardware comprises a materialize sub-operation.

17. The system of claim 12 , wherein the sub-operation for reading at least one data block from the memory hardware comprises a scan sub-operation.

18. The system of claim 17 , wherein determining that the determined sub-operations of the query comprise the scan sub-operation for reading the at least one data block comprises determining that the determined sub-operations of the query comprise only a single scan sub-operation for reading the at least one data block.

19. The system of claim 12 , wherein determining that the query comprises copying the plurality of the data blocks in the first table into the second table comprises:
determining an order of a list of columns associated with the query;
determining an order of columns of the first table; and
determining that the order of the list of columns associated with the query is the same as the order of columns of the first table.

20. The system of clai

Assignees

• Google Llc

Inventors

• Edara Pavan
• Tigani Jordan

Classifications

• G06F16/24537 Primary

  of operators · CPC title

• G06F16/24535 Primary

  of sub-queries or views · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2445

  Data retrieval commands; View definitions · CPC title