Method and system for performing query optimization using a hybrid execution plan
An improved method, system, and computer program product is disclosed that provides a hybrid approach to optimization which allows different subsets of data accessed by a query to be optimized with different access paths and execution approaches. Transformations may be performed to re-write the query, which restructures the query in a way that facilitates the hybrid optimization process. Multiple transformations may be interleaved to produce an efficient re-written query.
1. A computer implemented method for optimizing a query, comprising:
receiving a query that accesses a set of a data having a first subset accessible by index and an established second subset accessible by sequential scan and not accessible by index wherein the query is permitted to access the set of data by sequential scan and not by index;
using a processor to perform a transformation to re-write the query based on the first subset of data being accessible by index and the second subset of data being accessible by sequential scan and not accessible by index which results in a transformed query, wherein the transformed query includes a first query portion corresponding to the first subset that is permitted to access the first subset of data by index and a second query portion corresponding to the second subset that is permitted to access the second subset by sequential scan; and
optimizing the transformed query such that the first query portion corresponds to a first execution plan and the second query portion corresponds to a second execution plan, wherein the first execution plan is different from the second execution plan.
2. The method of claim 1 in which the query is transformed into the transformed query by taking an original query block and replicating the original query block into the first query portion and the second query portion connected by a union all statement.
3. The method of claim 1 in which the first subset of the data and the second subset of the data are partitions of a partitioned table.
4. The method of claim 3 in which partition groupings are analyzed to identify partitions that should be grouped together for a common execution plan.
5. The method of claim 3 in which partition pruning is performed to remove unnecessary partitions from consideration.
6. The method of claim 1 in which either the first and second execution plans comprise an access path that corresponds to an index access or a sequential scan.
7. The method of claim 6 in which index pruning is performed.
8. The method of claim 1 in which a validity check is performed for the transformation to determine whether the query can be transformed.
9. The method of claim 1 in which predicate generation is performed.
10. The method of claim 1 in which costs analysis is performed to identify an efficient transformation from among a group of candidate query formats.
11. The method of claim 1 in which selection of the transformed query is performed using a linear search strategy, exhaustive searching, iterative searching, and two pass searching.
12. The method of claim 1 in which multiple transformations are applied to generate the transformed query.
13. The method of claim 12 in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization.
14. A computer implemented method for executing a query, comprising:
receiving a transformed query that is intended to access a set of a data having an established first subset accessible by index and an established second subset accessible by sequential scan and not by index, wherein the transformed query is transformed from a query that is permitted to access the set of data by sequential scan and not by index;
wherein the query is transformed into the transformed query based on the first subset of data being accessible by index and a second subset of the data being accessible by sequential scan and not accessible by index, the transformed query including a first query portion that corresponding to the established first subset of data that is permitted to access the first subset by index and a second query portion corresponding to the established second subset data that is permitted to access the second subset by sequential scan; and
wherein the query has been optimized such that the first query portion corresponds to a first execution plan and the second query portion corresponds to a second execution plan; and
using a processor to execute the transformed query by using the first execution plan to access the first subset of data and using the second execution plan to access the second subset of data.
15. The method of claim 14 in which the first subset of the data and the second subset of the data are partitions of a partitioned table.
16. The method of claim 14 in which either the first and second execution plans comprise an access path that corresponds to an index access or a sequential scan.
17. The method of claim 14 in which the query resulted from multiple transformations that were applied to generate the query.
18. The method of claim 17 in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization.
19. A computer program product embodied on a non-transitory computer usable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a method for optimizing a query, the method comprising:
receiving a query that accesses a set of a data having an established first subset accessible by index and an established second subset accessible by sequential scan and not accessible by index, wherein the query access the set of data by sequential scan and not by index;
using a processor to perform a transformation to re-write the query based on the first subset of data being accessible by index and the second subset of data being accessible by sequential scan and not accessible by index which results in a transformed query, wherein the transformed query includes a first query portion corresponding to the first subset that is permitted to access the first subset of data by index and a second query portion corresponding to the second subset that is permitted to access the second subset by sequential scan; and
optimizing the transformed query such that the first query portion corresponds to a first execution plan and the second query portion corresponds to a second execution plan, wherein the first execution plan is different from the second execution plan.
20. The computer program product of claim 19 in which the query is transformed into the transformed query by taking an original query block and replicating the original query block into the first query portion and the second query portion connected by a union all statement.
21. The computer program product of claim 19 in which the first subset of the data and the second subset of the data are partitions of a partitioned table.
22. The computer program product of claim 19 in which either the first and second execution plan comprise an access path that corresponds to an index access or a sequential scan.
23. The computer program product of claim 19 in which a validity check is performed for the transformation to determine whether the query can be transformed.
24. The computer program product of claim 19 in which costs analysis is performed to identify an efficient transformation from among a group of candidate query formats.
25. The computer program product of claim 19 in which multiple transformations are applied to generate the transformed query.
26. The computer program product of claim 25 in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization.
27. A computer program product embodied on a non-transitory computer usable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a method for executing a query, the method comprising:
receiving a transformed query that is intended to access a set of a data having an established first subset accessible by index and an established second subset accessible by sequential scan and not by index, wherein the transformed query is transformed from a query that is permitted to access the set of data by sequential scan and not by index;
wherein the query is transformed into the transformed query based on the first subset of data being accessible by index and the second subset of data being accessible by sequential scan and not accessible by index, the transformed query including a first query portion corresponding to the established first subset of data that is permitted to access the first subset by index and a second query portion corresponding to the established second subset of the data that is permitted to access the second subset by sequential scan; and
wherein the transformed query has been optimized such that the first query portion corresponds to a first execution plan and the second query portion corresponds to a second execution plan; and
using a processor to execute the transformed query by using the first execution plan to access the established first subset of data and using the established second execution plan to access the second subset of data.
28. The computer program product of claim 27 in which the first subset of the data and the second subset of the data are partitions of a partitioned table.
29. The computer program product of claim 27 in which either the first and second execution plans comprise an access path that corresponds to an index access or a sequential scan.
30. The computer program product of claim 27 in which the query resulted from multiple transformations that were applied to generate the query.
31. The computer program product of claim 30 in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization.
32. A computer-based system for optimizing a query, comprising:
a computer processor to execute a set of program code instructions;
a memory to hold the program code instructions, in which the program code instructions for:
receiving a query that accesses a set of data having an established first subset accessible by index and an established second subset accessible by sequential scan and not accessible by index, wherein the query is permitted to access the set of data by sequential scan and not be index;
using a processor to perform a transformation to re-write the query based on the first subset of data being accessible by index and the second subset of data being accessible by sequential scan and not accessible by index which results in a transformed query, wherein the transformed query includes a first query portion corresponding to the first subset that is permitted to access the first subset of data by index and a second query portion corresponding to the second subset that is permitted to access the second subset by sequential scan; and
optimizing the transformed query such that the first query portion corresponds to a first execution plan and the second query portion corresponds to a second execution plan, wherein the first execution plan is different from the second execution plan.
33. The system of claim 32 in which the query is transformed into the transformed query by taking an original query block and replicating the original query block into the first query portion and the second query portion connected by a union all statement.
34. The system of claim 32 in which the first subset of the data and the second subset of the data are partitions of a partitioned table.
35. The system of claim 32 in which either the first and second execution plans comprise an access path that corresponds to an index access or a sequential scan.
36. The system of claim 32 in which costs analysis is performed to identify an efficient transformation from among a group of candidate query formats.
37. The system of claim 32 in which multiple transformations are applied to generate the transformed query.
38. The system of claim 37 in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization.
39. A computer-based system for executing a query, comprising:
a computer processor to execute a set of program code instructions;
a memory to hold the program code instructions, in which the program code instructions for:
receiving a transformed query that is intended to access a set of a data having an established first subset accessible by index and an established second subset accessible by sequential scan and not by index, wherein the transformed query is transformed from a query that is permitted to access the set of data by sequential scan and not by index;
wherein the query is transformed into the transformed query based on the first subset of data being accessible by index and the second subset of data being accessible by sequential scan and not accessible by index, the transformed query including a first query portion corresponding to the established first subset of data that is permitted to access the first subset by index and a second query portion corresponding to the established second subset of the data that is permitted to access the second subset by sequential scan; and
wherein the transformed query has been optimized such that the first query portion corresponds to a first execution plan and the second query portion corresponds to a second execution plan; and
using a processor to execute the transformed query by using the first execution plan to access the established first subset of data and using the established second execution plan to access the second subset of data.
40. The system of claim 39 in which the first subset of the data and the second subset of the data are partitions of a partitioned table.
41. The system of claim 39 in which either the first and second execution plans comprise an access path that corresponds to an index access or a sequential scan.
42. The system of claim 39 in which the query resulted from multiple transformations that were applied to generate the query.
43. The system of claim 42 in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization.