Efficient processing of multi-column and function-based in-list predicates

Abstract

Described herein are approaches for generating execution plans for database commands that include an in-list predicate. The approaches can be used to generate execution plans that exploit the power of in-list iterators in ways and under circumstances not previously supported by conventional DBMSs. An in-list iterator may be used with execution subplans for processing multi-column in-list queries. An in-list iterator is used with execution subplans that scan function-based indexes. The execution plans for a multi-column in-list query limit table scans to only table partitions that contain data that satisfy the query.

What is claimed is:  
     
         1 . A method for creating execution plans, the method comprising the steps of: 
 receiving a database command that contains a predicate based on an IN operator;    wherein said database command references a table that is partitioned into a plurality of partitions based on a partition key;    wherein a first argument of said IN operator is based on a multi-column in-list;    wherein a second argument of said IN operator references the partition key; and    determining an execution plan based on said database command, said execution plan including a first plan operator, said first plan operator specifying operations that limit scanning to partitions having rows containing a partition key value in said multi-column in-list.    
     
     
         2 . The method of  claim 1 , wherein said second argument references another column in addition to said partition key.  
     
     
         3 . The method of  claim 1 , wherein said table is partitioned based on another key.  
     
     
         4 . A computer-readable medium carrying one or more sequences of instructions for creating execution plans, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of: 
 receiving a database command that contains a predicate based on an IN operator;    wherein said database command references a table that is partitioned into a plurality of partitions based on a partition key;    wherein a first argument of said IN operator is based on a multi-column in-list;    wherein a second argument of said IN operator references the partition key; and    determining an execution plan based on said database command, said execution plan including a first plan operator, said first plan operator specifying operations that limit scanning to partitions having rows containing a partition key value in said multi-column in-list.    
     
     
         5 . The computer-readable medium of  claim 4 , wherein said second argument references another column in addition to said partition key.  
     
     
         6 . The computer-readable medium of  claim 4 , wherein said table is partitioned based on another key.