Optimizing queries using predicate mappers

Abstract

Provided are a computer implemented method, computer program product, and system for re-writing a query. A query including a query predicate is received. It is determined that the query predicate includes one column with an associated predicate mapper. An equivalent predicate is generated for the query predicate using the predicate mapper that provides a mapping from the one column to another column. A cost of the query with the equivalent predicate is evaluated. In response to determining that the cost of the query is optimal, the query is rewritten to replace the query predicate with the equivalent predicate.

1 - 8 . (canceled) 
     
     
         9 . A computer program product for re-writing a query, the computer program product comprising:
 a computer readable storage medium having computer readable program code embodied therein, wherein the computer readable program code, when executed by a processor of a computer, is configured to perform operations of:
 receiving a query including a query predicate; 
 determining that the query predicate includes one column with an associated predicate mapper, wherein the predicate mapper creates a mapping from one column to a base column; 
 generating an equivalent predicate for the query predicate using the predicate mapper that provides a mapping from the one column to another column, wherein the equivalent predicate sets the base column equal to a value returned by the predicate mapper; 
 evaluating a cost of the query with the equivalent predicate; and 
 in response to determining that the cost of the query is optimal, re-writing the query to replace the query predicate with the equivalent predicate. 
   
     
     
         10 . The computer program product of  claim 9 , wherein the one column comprises a generated column. 
     
     
         11 . The computer program product of  claim 10 , wherein the computer readable program code, when executed by the processor of the computer, is configured to perform operations of:
 locating an inverse function for a mathematical function that defines the generated column in a rules repository; and   creating the predicate mapper using the inverse function.   
     
     
         12 . The computer program product of  claim 10 , wherein the computer readable program code, when executed by the processor of the computer, is configured to perform operations of:
 determining that a mathematical function that defines the generated column is invertible;   finding an inverse function for the mathematical function using mathematical equations; and   creating the predicate mapper using the inverse function.   
     
     
         13 . The computer program product of  claim 12  wherein the mathematical function that defines the generated column is invertible if each element in a generated column set corresponds to a unique value in a base column set. 
     
     
         14 . The computer program product of  claim 9 , wherein the predicate mapper comprises a schema object. 
     
     
         15 . The computer program product of  claim 9 , wherein the predicate mapper is created by a user. 
     
     
         16 . The computer program product of  claim 9 , wherein the predicate mapper is created by a database engine. 
     
     
         17 . A computer system for re-writing a query, comprising:
 a processor; and   a storage device coupled to the processor, wherein the storage device has stored thereon a program, and wherein the processor is configured to execute instructions of the program to perform operations, wherein the operations comprise:
 receiving a query including a query predicate; 
 determining that the query predicate includes one column with an associated predicate mapper, wherein the predicate mapper creates a mapping from one column to a base column; 
 generating an equivalent predicate for the query predicate using the predicate mapper that provides a mapping from the one column to a another column, wherein the equivalent predicate sets the base column equal to a value returned by the predicate mapper; 
 evaluating a cost of the query with the equivalent predicate; and 
 in response to determining that the cost of the query is optimal, re-writing the query to replace the query predicate with the equivalent predicate. 
   
     
     
         18 . The computer system of  claim 17 , wherein the one column comprises a generated column. 
     
     
         19 . The computer system of  claim 18 , wherein the operations further comprise:
 locating an inverse function for a mathematical function that defines the generated column in a rules repository; and   creating the predicate mapper using the inverse function.   
     
     
         20 . The computer system of  claim 18 , wherein the operations further comprise:
 determining that a mathematical function that defines the generated column is invertible;   finding an inverse function for the mathematical function using mathematical equations; and   creating the predicate mapper using the inverse function.   
     
     
         21 . The computer system of  claim 20 , wherein the mathematical function that defines the generated column is invertible if each element in a generated column set corresponds to a unique value in a base column set. 
     
     
         22 . The computer system of  claim 17 , wherein the predicate mapper comprises a schema object. 
     
     
         23 . The computer system of  claim 17 , wherein the predicate mapper is created by a user. 
     
     
         24 . The computer system of  claim 17 , wherein the predicate mapper is created by a database engine.