TRANSFORMING A QUERY TO REUSE STORED DATA

• US 20150088919A1
• Filed: 09/20/2013
• Published: 03/26/2015
• Est. Priority Date: 09/20/2013
• Status: Abandoned Application

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: HUNTER, James, KOCIUBES, Adam, SOYLEMEZ, Ekrem S.C.
• Application Number:
  US14/033,358
• Publication Number:
  US 20150088919A1
• Time in Patent Office: Days
• US Class Current: 707/759
• CPC Class Codes: G06F 16/24535 of sub-queries or views

Abstract

Methods, computer systems, and stored instructions are described herein for transforming a query or execution plan to re-use stored data. A query processor stores a query or a representation thereof. Before executing the query, the query processor analyzes the query or representation to determine whether the query or representation could cause at least part of a data structure to be generated at least twice. Based at least in part on determining that the at least part of the data structure could be generated at least twice by the query or representation, the query processor transforms the query or representation. The transformed query or representation includes a first transformed sub-query or sub-operation that generates and stores the at least part of the data structure, and a second transformed sub-query or sub-operation uses the at least part of the data structure that was stored by the first transformed sub-query or sub-operation.

Claims

1. A method comprising:

storing a query;

before executing the query, analyzing content of the query to determine whether the query contains two or more sub-queries that, if executed, could cause generation of at least part of a data structure;

based at least in part on determining that the query contains the two or more sub-queries, transforming the query to a transformed query comprising;

a first transformed sub-query that, when executed, creates and stores the at least part of the data structure, and

at least a second transformed sub-query that, when executed, uses the at least part of the data structure stored by the first transformed sub-query;

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

2. The method of claim 1, wherein the first transformed sub-query, when executed, causes generation of the at least part of the data structure at least in part by determining set identifiers that identify unique combinations of values that occur in two or more dimension columns referenced by at least the first sub-query.

3. The method of claim 1, wherein the first transformed sub-query, when executed, causes generation of the at least part of the data structure at least in part by aggregating values from one or more database objects referenced by at least the first sub-query.

4. The method of claim 1, further comprising creating and re-using the at least part of the data structure at least in part by executing the transformed query.

5. The method of claim 1, further comprising determining that generation of the at least part of the data structure involves a threshold amount of analytical processing;

wherein transforming the query to the transformed query is also based at least in part on determining that generation of the at least part of the data structure involves the threshold amount of analytical processing.

6. The method of claim 1, wherein analyzing the content of the query comprises determining whether different sub-queries of the query satisfy stored conditions stored in association with candidate transformations;

wherein transforming the query to the transformed query is also based at least in part on determining that at least two of the different sub-queries satisfy one or more of the stored conditions.

7. The method of claim 1, wherein the first transformed sub-query, when executed, causes the at least part of the data structure to be stored separately from a results cache that is used during execution of the query to cache results for the query, and wherein the at least part of the data structure is retained in storage until after the at least part of the data structure has been used for at least the second transformed sub-query.

8. The method of claim 1, wherein the at least part of the data structure is not referenced by the received query and would not have been generated by the received query without transformation;

wherein the first sub-query comprises a complex operation for which a candidate transformation creates and uses the at least part of the data structure, and wherein the second sub-query comprises the complex operation.

9. A method comprising:

receiving a representation of a query;

before executing the representation, analyzing the representation to determine whether the representation, if executed, could cause two or more sub-operations to generate at least part of a data structure;

based at least in part on determining that the representation contains the two or more operations, transforming the representation into a transformed representation that, when performed, causes

a first transformed sub-operation that creates and stores the at least part of the data structure, and

at least a second transformed sub-operation that uses the at least part of the data structure stored by the first transformed sub-operation;

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

10. The method of claim 9, wherein the first transformed sub-operation, when performed, causes generation of the at least part of the data structure at least in part by determining set identifiers that identify unique combinations of values that occur in two or more dimension columns on which the first sub-operation operates.

11. The method of claim 9, wherein the first transformed sub-operation, when performed, causes generation of the at least part of the data structure at least in part by aggregating values from one or more database objects into the at least part of the data structure.

12. The method of claim 9, further comprising creating and re-using the at least part of the data structure at least in part by performing the transformed representation, wherein the transformed representation is an execution plan.

13. The method of claim 9, wherein the at least part of the data structure is not referenced by the received representation and would not have been generated by the received representation without transformation;

wherein the first sub-operation comprises a complex operation for which a candidate transformation creates and uses the at least part of the data structure, and wherein the second sub-operation comprises the complex operation.

14. One or more non-transitory computer-readable storage media storing instructions which, when executed, cause:

storing a query;

before executing the query, analyzing content of the query to determine whether the query contains two or more sub-queries that, if executed, could cause generation of at least part of a data structure;

based at least in part on determining that the query contains the two or more sub-queries, transforming the query to a transformed query comprising;

a first transformed sub-query that, when executed, creates and stores the at least part of the data structure, and

at least a second transformed sub-query that, when executed, uses the at least part of the data structure stored by the first transformed sub-query.

15. The one or more non-transitory computer-readable storage media of claim 14, wherein the first transformed sub-query, when executed, causes generation of the at least part of the data structure at least in part by determining set identifiers that identify unique combinations of values that occur in two or more dimension columns referenced by at least the first sub-query.

16. The one or more non-transitory computer-readable storage media of claim 14, wherein the first transformed sub-query, when executed, causes generation of the at least part of the data structure at least in part by aggregating values from one or more database objects referenced by at least the first sub-query.

17. The one or more non-transitory computer-readable storage media of claim 14, further comprising creating and re-using the at least part of the data structure at least in part by executing the transformed query.

18. The one or more non-transitory computer-readable storage media of claim 14, further comprising determining that generation of the at least part of the data structure involves a threshold amount of analytical processing;

wherein transforming the query to the transformed query is also based at least in part on determining that generation of the at least part of the data structure involves the threshold amount of analytical processing.

19. The one or more non-transitory computer-readable storage media of claim 14, wherein analyzing the content of the query comprises determining whether different sub-queries of the query satisfy stored conditions stored in association with candidate transformations;

wherein transforming the query to the transformed query is also based at least in part on determining that at least two of the different sub-queries satisfy one or more of the stored conditions.

20. The one or more non-transitory computer-readable storage media of claim 14, wherein the first transformed sub-query, when executed, causes the at least part of the data structure to be stored separately from a results cache that is used during execution of the query to cache results for the query, and wherein the at least part of the data structure is retained in storage until after the at least part of the data structure has been used for at least the second transformed sub-query.

21. The one or more non-transitory computer-readable storage media of claim 14, wherein the at least part of the data structure is not referenced by the received query and would not have been generated by the received query without transformation;

wherein the first sub-query comprises a complex operation for which a candidate transformation creates and uses the at least part of the data structure, and wherein the second sub-query comprises the complex operation.

22. One or more non-transitory computer-readable storage media storing instructions which, when executed, cause:

receiving a representation of a query;

before executing the representation, analyzing the representation to determine whether the representation, if executed, could cause two or more sub-operations to generate at least part of a data structure;

based at least in part on determining that the representation contains the two or more operations, transforming the representation into a transformed representation that, when performed, causes

a first transformed sub-operation that creates and stores the at least part of the data structure, and

at least a second transformed sub-operation that uses the at least part of the data structure stored by the first transformed sub-operation.

23. The one or more non-transitory computer-readable storage media of claim 22, wherein the first transformed sub-operation, when performed, causes generation of the at least part of the data structure at least in part by determining set identifiers that identify unique combinations of values that occur in two or more dimension columns on which the first sub-operation operates.

24. The one or more non-transitory computer-readable storage media of claim 22, wherein the first transformed sub-operation, when performed, causes generation of the at least part of the data structure at least in part by aggregating values from one or more database objects into the at least part of the data structure.

25. The one or more non-transitory computer-readable storage media of claim 22, further comprising creating and re-using the at least part of the data structure at least in part by performing the transformed representation, wherein the transformed representation is an execution plan.

26. The one or more non-transitory computer-readable storage media of claim 22, wherein the at least part of the data structure is not referenced by the received representation and would not have been generated by the received representation without transformation;

wherein the first sub-operation comprises a complex operation for which a candidate transformation creates and uses the at least part of the data structure, and wherein the second sub-operation comprises the complex operation.