Virtual columns

Abstract

Techniques are described herein for performing column functions on virtual columns in database tables. A virtual column is defined by the database to contain results of a defining expression. Statistics are collected and maintained for virtual columns. Indexing is performed on virtual columns. Referential integrity is maintained between two tables using virtual columns as keys. Join predicate push-down operations are also performed using virtual columns.

1 . A computer-implemented method for maintaining referential integrity between two tables, comprising:
 receiving a designation that a first column in a first table is a primary key,
 wherein the first column is a virtual column, 
 wherein a DBMS defines the virtual column as containing results of an expression, and 
 wherein a virtual column comprises at least one data value computed from an expression or a function; 
   receiving a designation that a second column in a second table is a foreign key; and   maintaining referential integrity between data values in the first column and data values in the second column.   
     
     
         2 . The computer-implemented method of  claim 1 , wherein the step of maintaining referential integrity comprises:
 removing, from the second table, any rows whose value in the second column does not correspond to a value in the first column.   
     
     
         3 . The computer-implemented method of  claim 1 , wherein the step of maintaining referential integrity comprises:
 receiving a request to add a row to the second table,   determining whether adding the row to the second table results in adding a value to the second column where the value does not correspond to a value in the first column,   in response to determining that adding the row to the second table results in adding a value to the second column where the value does not correspond to a value in the first column, declining to process the request to add the row to the second table.   
     
     
         4 . The computer-implemented method of  claim 3 , wherein the step of declining to process the request further comprises generating an error message. 
     
     
         5 . A computer-implemented method, comprising:
 generating a transformed query based on a particular query, wherein said particular query includes:
 an outer query; 
 a view within a FROM list of the outer query; 
 a join predicate that references:
 a virtual column of an outer table of the outer query, and 
 a column returned by the view, 
 wherein a DBMS defines the virtual column as containing results of an expression, and 
 wherein a virtual column comprises at least one data value computed from an expression or a function; 
 
 wherein generating the transformed query includes pushing down the join predicate to create a pushed down join predicate that references the column of the outer table and a certain column returned by the view is based. 
   
     
     
         6 . A non-transitory computer-readable storage medium storing instructions for maintaining referential integrity between two tables, the instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:
 receiving a designation that a first column in a first table is a primary key,
 wherein the first column is a virtual column, 
 wherein a DBMS defines the virtual column as containing results of an expression, and 
 wherein a virtual column comprises at least one data value computed from an expression or a function; 
   receiving a designation that a second column in a second table is a foreign key; and   maintaining referential integrity between data values in the first column and data values in the second column.   
     
     
         7 . The non-transitory computer-readable storage medium of  claim 6 , wherein the step of maintaining referential integrity comprises:
 removing, from the second table, any rows whose value in the second column does not correspond to a value in the first column.   
     
     
         8 . The non-transitory computer-readable storage medium of  claim 6 , wherein the step of maintaining referential integrity comprises:
 receiving a request to add a row to the second table,   determining whether adding the row to the second table results in adding a value to the second column where the value does not correspond to a value in the first column,   in response to determining that adding the row to the second table results in adding a value to the second column where the value does not correspond to a value in the first column, declining to process the request to add the row to the second table.   
     
     
         9 . The non-transitory computer-readable storage medium of  claim 8 , wherein the step of declining to process the request further comprises generating an error message. 
     
     
         10 . A non-transitory computer-readable storage medium storing instructions, the instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:
 generating a transformed query based on a particular query, wherein said particular query includes:
 an outer query; 
 a view within a FROM list of the outer query; 
 a join predicate that references:
 a virtual column of an outer table of the outer query, and 
 a column returned by the view, 
 wherein a DBMS defines the virtual column as containing results of an expression, and 
 wherein a virtual column comprises at least one data value computed from an expression or a function; 
 
 wherein generating the transformed query includes pushing down the join predicate to create a pushed down join predicate that references the column of the outer table and a certain column returned by the view is based.