Query plans for analytic SQL constructs
A system and method for managing data storage and data access with querying data in a distributed system without buffering the results on intermediate operations in disk storage.
1. A non-transitory computer-readable medium storing instructions which, when executed by one or more third set of one or more processors of a computing system, cause the computing system to:
receive a query pertaining to a set of data stored in one or more databases;
access a query execution plan comprising one or more steps on the set of data referenced by the query;
initiate, within a first set of one or more processors, a first parallel execution process in the query execution plan to process the set of data and generate an intermediate result of a query;
provide, by the first processor, during execution of the query execution plan, the intermediate result to a plurality of a second set of processors for processing a plurality of secondary parallel execution processes by the plurality of the second set of processors, wherein each of the first set and the plurality of the second set of processors are decoupled from a disk storage;
initiate, by one or more of the second set of processors, the plurality of secondary parallel execution processes to process the intermediate result to generate a plurality of secondary results; and
store a final result to disk storage within a storage platform.
2. The non-transitory computer-readable medium of claim 1 , wherein the plurality of secondary results is generated without writing the intermediate result to disk storage.
3. The non-transitory computer-readable medium of claim 1 , wherein each of the plurality of secondary parallel execution processes the intermediate result using a different operation.
4. The non-transitory computer-readable medium of claim 1 , wherein the instructions further cause the computing system to:
delay operation of at least one of the plurality of secondary parallel execution processes so as to coordinate timing among other secondary parallel execution processes of the plurality of secondary parallel execution processes.
5. The non-transitory computer-readable medium of claim 1 , wherein the intermediate result comprises a plurality of rows of database data.
6. The non-transitory computer-readable medium of claim 1 , wherein each of the plurality of secondary parallel execution processes are unique operators.
7. The non-transitory computer-readable medium of claim 1 , wherein the intermediate result is not materialized.
8. The non-transitory computer-readable medium of claim 1 , wherein the intermediate result generated by the first parallel execution processes is not materialized to a temporary structure.
9. The non-transitory computer-readable medium of claim 1 , wherein the one or more databases are coupled to a database system that includes a relational database.
10. The non-transitory computer-readable medium of claim 9 , wherein the relational database is a structured query language database.
11. The non-transitory computer-readable medium of claim 1 , wherein the one or more databases are coupled to a database system is a multi-tenant database that isolates computing resources and data between different customers.
12. The non-transitory computer-readable medium of claim 1 , wherein the each of first and the plurality of second processors is coupled to the disk storage via a communications network.
13. The non-transitory computer-readable medium of claim 1 , wherein the each of the first set of one or more processors and the second set of one or more processors includes a local cache.
14. The non-transitory computer-readable medium of claim 1 , wherein the computing system comprises a virtual data warehouse.
15. A method comprising:
receiving a query pertaining to a set of data stored in one or more databases;
accessing a query execution plan comprising one or more steps to be performed on a set of data pertaining to the query;
initiating, within one or more of a first set of processors, a first parallel execution process in the query execution plan to process the set of data and generate an intermediate result of a query;
providing, by the one or more of the first set of processors, during execution of the query execution plan, the intermediate result to a plurality of a second set of processors for processing a plurality of secondary parallel execution processes within the plurality of the second set of processors, wherein each of the first set of processors and the plurality of the second set of processors is decoupled from a disk storage;
initiating, by the one or more of the plurality of the second set of processors, the plurality of secondary parallel execution processes to process the intermediate result to generate a plurality of secondary results; and
storing a final result to disk storage within a storage platform.
16. The method of claim 15 , wherein the plurality of secondary results generated without writing the intermediate result to disk storage.
17. The method of claim 15 , wherein each of the plurality of secondary parallel execution processes process the intermediate result using a different operation.
18. The method of claim 15 , further comprising:
delaying operation of at least one of the plurality of secondary parallel execution processes so as to coordinate timing among other secondary parallel execution processes of the plurality of secondary parallel execution processes.
19. The method of claim 15 , wherein the intermediate result comprises a plurality of rows of database data.
20. The method of claim 15 , wherein each of the plurality of secondary parallel execution processes are unique operators.
21. The method of claim 15 , wherein the intermediate result is not materialized.
22. The method of claim 15 , wherein the intermediate result generated by the first parallel execution processes is not materialized to a temporary structure.
23. The method of claim 15 , wherein the set of data is stored in a relational database.
24. The method of claim 23 , wherein the relational database is a structured query language database.
25. The method of claim 15 , wherein the one or more databases comprise a multi-tenant database that isolates computing resources and data between different customers.
26. The method of claim 15 , wherein the each of the first set of one or more processors and the second set of one or more processors is coupled to the disk storage via a communications network.
27. The method of claim 15 , wherein the each of the first set of one or more processors and the second set of one or more processors includes a local cache.
28. A system comprising:
a query coordinating process programmed to:
receive a query pertaining to a set of data stored in one or more databases, and
access a query execution plan comprising one or more steps on the set of data referenced by the query;
a first set of processors programmed to:
initiate a first parallel execution process in the query execution plan to process the set of data and generate an intermediate result of the query, and
provide, during execution of the query execution plan, the intermediate result to a plurality of a second set of processors for concurrent processing by the plurality of the second set of processors, wherein each of the first set of processors and the plurality of the second set of processors is decoupled from a disk storage; and
the plurality of the second set of processors programmed to initiate a plurality of secondary parallel execution processes to process the intermediate result to generate a plurality of secondary results; and
a storage platform programmed to store a final result to disk storage.
29. The system of claim 28 , wherein the plurality of secondary results is generated without writing the intermediate result to the disk storage.
30. The system of claim 28 , wherein the query coordinating process further to:
delay operation of at least one of the plurality of secondary parallel execution processes so as to coordinate timing among other secondary parallel execution processes of the plurality of secondary parallel execution processes.