Optimizing a query to a partitioned database table using a virtual maintained temporary index that spans multiple database partitions

Abstract

A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

The invention claimed is: 
     
       1. An apparatus comprising:
 at least one processor; 
 a memory coupled to the at least one processor; and 
 a query optimizer residing in the memory and executed by the at least one processor, the query optimizer generating a virtual maintained temporary index that spans a plurality of partitions in a partitioned database table, wherein the query optimizer creates an access plan for a query that references the partitioned database table, wherein the access plan references the virtual maintained temporary index, wherein the query optimizer registers ownership of a virtual maintained temporary index by an access plan when the virtual maintained temporary index is referenced in the access plan, wherein the query optimizer disables deletion of the virtual maintained temporary index when the virtual maintained temporary index is owned by at least one access plan, wherein the query optimizer enables deletion of the virtual maintained temporary index when the virtual maintained temporary index is not owned by any access plan. 
 
     
     
       2. The apparatus of  claim 1  wherein the query optimizer determines which of the plurality of partitions have common characteristics, wherein the virtual maintained temporary index spans a plurality of partitions that have common characteristics. 
     
     
       3. The apparatus of  claim 2  wherein the virtual maintained temporary index comprises a plurality of maintained temporary indexes over the plurality of partitions that have common characteristics. 
     
     
       4. The apparatus of  claim 2  wherein the virtual maintained temporary index comprises a single maintained temporary index over the plurality of partitions that have common characteristics. 
     
     
       5. A computer-readable program product comprising:
 a query optimizer that generates a virtual maintained temporary index that spans a plurality of partitions in a partitioned database table, wherein the query optimizer creates an access plan for a query that references the partitioned database table, wherein the access plan references the virtual maintained temporary index, wherein the query optimizer registers ownership of a virtual maintained temporary index by an access plan when the virtual maintained temporary index is referenced in the access plan, wherein the query optimizer disables deletion of the virtual maintained temporary index when the virtual maintained temporary index is owned by at least one access plan, wherein the query optimizer enables deletion of the virtual maintained temporary index when the virtual maintained temporary index is not owned by any access plan; and 
 recordable media bearing the query optimizer. 
 
     
     
       6. The program product of  claim 5  wherein the query optimizer determines which of the plurality of partitions have common characteristics, wherein the virtual maintained temporary index spans a plurality of partitions that have common characteristics. 
     
     
       7. The program product of  claim 6  wherein the virtual maintained temporary index comprises a plurality of maintained temporary indexes over the plurality of partitions that have common characteristics. 
     
     
       8. The program product of  claim 6  wherein the virtual maintained temporary index comprises a single maintained temporary index over the plurality of partitions that have common characteristics. 
     
     
       9. An apparatus comprising:
 at least one processor; 
 a memory coupled to the at least one processor; and 
 a query optimizer residing in the memory and executed by the at least one processor, the query optimizer generating a virtual maintained temporary index that spans a plurality of partitions in a partitioned database table, wherein the query optimizer creates an access plan for a query that references the partitioned database table, wherein the access plan references the virtual maintained temporary index, wherein the query optimizer determines which of the plurality of partitions have common characteristics, wherein the virtual maintained temporary index spans a plurality of partitions that have common characteristics, wherein the virtual maintained temporary index comprises a plurality of maintained temporary indexes over the plurality of partitions that have common characteristics, wherein the query optimizer registers ownership of a virtual maintained temporary index by an access plan when the virtual maintained temporary index is referenced in the access plan, wherein the query optimizer disables deletion of the virtual maintained temporary index when the virtual maintained temporary index is owned by at least one access plan, wherein the query optimizer enables deletion of the virtual maintained temporary index when the virtual maintained temporary index is not owned by any access plan.