Method and system for performing query optimization using a hybrid execution plan

Abstract

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.

What is claimed is: 
     
         1 . A computer implemented method for optimizing a query, comprising:
 receiving a query that accesses a set of a data having a first subset stored in a first type of storage medium and a second subset of the data stored in a second type of storage medium;   using a processor to perform a transformation to re-write the query which results in a transformed query, wherein the transformed query comprises a first query portion that corresponds to the first subset that is stored in the first type of storage medium and a second query portion that corresponds to the second subset that is stored in the second type of storage medium; 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 . The method of  claim 1 , wherein the first or second type of storage medium is a fast storage medium. 
     
     
         15 . The method of  claim 1 , wherein the first or second type of storage medium is a slow storage medium. 
     
     
         16 . The method of  claim 1 , wherein the first or second type of storage medium is a local storage medium. 
     
     
         17 . The method of  claim 1 , wherein the first or second type of storage medium is a remote storage medium. 
     
     
         18 . 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 a data having a first subset of the data corresponding to a first type of storage medium and a second subset of the data corresponding to a second type of storage medium;   using a processor to perform a transformation to re-write the query which results in a transformed query, wherein the transformed query comprises a first query portion that corresponds to the first subset associated with the first type of storage medium and a second query portion that corresponds to the second subset associated with the second type of storage medium; 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.   
     
     
         19 . A computer program product embodied on a 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 a first subset stored in a first type of storage medium and a second subset of the data stored in a second type of storage medium;   using a processor to perform a transformation to re-write the query which results in a transformed query, wherein the transformed query comprises a first query portion that corresponds to the first subset that is stored in the first type of storage medium and a second query portion that corresponds to the second subset that is stored in the second type of storage medium; 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 . A computer implemented method for optimizing a query, comprising:
 receiving a query that accesses a set of a data having a first subset of the data corresponding to a first type of compression and a second subset of the data corresponding to a second type of compression;   using a processor to perform a transformation to re-write the query which results in a transformed query, wherein the transformed query comprises a first query portion that corresponds to the first subset associated with the first type of compression and a second query portion that corresponds to the second subset associated with the second type of compression; 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.   
     
     
         21 . The method of  claim 20  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. 
     
     
         22 . The method of  claim 20  in which the first subset of the data and the second subset of the data are partitions of a partitioned table. 
     
     
         23 . The method of  claim 20  in which partition groupings are analyzed to identify partitions that should be grouped together for a common execution plan. 
     
     
         24 . The method of  claim 20  in which partition pruning is performed to remove unnecessary partitions from consideration. 
     
     
         25 . The method of  claim 20  in which either the first and second execution plans comprise an access path that corresponds to an index access or a sequential scan. 
     
     
         26 . The method of  claim 20  in which index pruning is performed. 
     
     
         27 . The method of  claim 20  in which a validity check is performed for the transformation to determine whether the query can be transformed. 
     
     
         28 . The method of  claim 20  in which predicate generation is performed. 
     
     
         29 . The method of  claim 20  in which costs analysis is performed to identify an efficient transformation from among a group of candidate query formats. 
     
     
         30 . The method of  claim 20  in which selection of the transformed query is performed using a linear search strategy, exhaustive searching, iterative searching, and two pass searching. 
     
     
         31 . The method of  claim 20  in which multiple transformations are applied to generate the transformed query. 
     
     
         32 . The method of  claim 31  in which the multiple transformations comprise a combination of table expansion, star transformation, or join factorization. 
     
     
         33 . A computer program product embodied on a 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 a first subset of the data corresponding to a first type of compression property and a second subset of the data corresponding to a second type of compression property;   using a processor to perform a transformation to re-write the query which results in a transformed query, wherein the transformed query comprises a first query portion that corresponds to the first subset associated with the first type of compression property and a second query portion that corresponds to the second subset associated with the second type of compression property; 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.   
     
     
         34 . 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 a data having a first subset of the data corresponding to a first type of compression and a second subset of the data corresponding to a second type of compression;   using a processor to perform a transformation to re-write the query which results in a transformed query, wherein the transformed query comprises a first query portion that corresponds to the first subset associated with the first type of compression and a second query portion that corresponds to the second subset associated with the second type of compression; 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.