Apparatus and method for 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.

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.    
   
   
       2 . The apparatus of  claim 1  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.  
   
   
       3 . 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.  
   
   
       4 . The apparatus of  claim 3  wherein the virtual maintained temporary index comprises a plurality of maintained temporary indexes over the plurality of partitions that have common characteristics.  
   
   
       5 . The apparatus of  claim 3  wherein the virtual maintained temporary index comprises a single maintained temporary index over the plurality of partitions that have common characteristics.  
   
   
       6 . The apparatus of  claim 1  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.  
   
   
       7 . The apparatus of  claim 1  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.  
   
   
       8 . The apparatus of  claim 1  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.  
   
   
       9 . A computer-implemented method for optimizing a database query that references a partitioned database table that includes a plurality of partitions, the method comprising the steps of: 
 determining which of the plurality of partitions share common characteristics;    generating a virtual maintained temporary index that spans a plurality of partitions that share the common characteristics; and    referencing the virtual maintained temporary index in an access plan for the database query.    
   
   
       10 . The method of  claim 9  further comprising the step of an access plan registering ownership of a virtual maintained temporary index when the virtual maintained temporary index is referenced in the access plan.  
   
   
       11 . The method of  claim 9  further comprising the step of disabling deletion of the virtual maintained temporary index when the virtual maintained temporary index is owned by at least one access plan.  
   
   
       12 . The method of  claim 9  further comprising the steps of enabling deletion of the virtual maintained temporary index when the virtual maintained temporary index is not owned by any access plan.  
   
   
       13 . 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; and    recordable media bearing the query optimizer.    
   
   
       14 . The program product of  claim 13  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.  
   
   
       15 . The program product of  claim 13  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.  
   
   
       16 . The apparatus of  claim 15  wherein the virtual maintained temporary index comprises a plurality of maintained temporary indexes over the plurality of partitions that have common characteristics.  
   
   
       17 . The apparatus of  claim 15  wherein the virtual maintained temporary index comprises a single maintained temporary index over the plurality of partitions that have common characteristics.  
   
   
       18 . The program product of  claim 13  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.  
   
   
       19 . The program product of  claim 13  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.  
   
   
       20 . The program product of  claim 13  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.