Virtual columns
AssigneeOracle International Corporation
InventorsSubhransu Basu, Harmeek Singh Bedi
Life of this patent
Estimating the term…
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.
Claims6
A computer-implemented method comprising:
- receiving one or more database commands that request to define a virtual column that contains results of a virtual column expression, said one or more database commands specifying:
- a name for said virtual column;
- and
- said virtual column expression;
- in response to receiving said one or more database commands, generating database metadata that:
- defines said virtual column as a named virtual column having said name;
- and
- defines said virtual column as containing the results of said virtual column expression;
- generating statistical data on a distribution of data values contained in said virtual column;
- receiving a query that includes at least one predicate,
- wherein the at least one predicate references said virtual column by said name;
- in response to receiving the query:
- retrieving, from a statistics database, the statistical data on a distribution of data values contained in said virtual column;
- estimating, based on the statistical data, a cost of executing the query;
- and
- computing results for the query, wherein computing results for the query includes computing one or more values for said virtual column according to said virtual column expression;
- and
- wherein the steps are performed by one or more computing devices.
A computer-implemented method comprising:
- receiving one or more database commands that request to define a virtual column that contains results of a virtual column expression, said one or more database commands specifying:
- a name for said virtual column;
- said virtual column expression;
- and
- that said virtual column is an index key for an index that indexes data values contained in said virtual column;
- in response to receiving said one or more database commands, generating database metadata that:
- defines said virtual column as a named virtual column having said name;
- defines said virtual column as containing the results of said virtual column expression;
- and
- defines said virtual column as said index key for said index;
- receiving a query that references said virtual column by name;
- retrieving data from said index;
- generating results for the query based on said data from said index;
- and
- wherein the steps are performed by one or more computing devices.
A non-transitory computer-readable storage medium storing instructions for evaluating queries, the instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform:
- receiving one or more database commands that request to define a virtual column that contains results of a virtual column expression, said one or more database commands specifying:
- a name for said virtual column;
- and
- said virtual column expression;
- in response to receiving said one or more database commands, generating database metadata that:
- defines said virtual column as a named virtual column having said name;
- and
- defines said virtual column as containing the results of said virtual column expression;
- generating statistical data on a distribution of data values contained in said virtual column;
- receiving a query that includes at least one predicate, wherein the at least one predicate references said virtual column by said name;
- in response to receiving the query:
- retrieving, from a statistics database, the statistical data on a distribution of data values contained in said virtual column;
- estimating, based on the statistical data, a cost of executing the query;
- and
- computing results for the query, wherein computing results for the query includes computing one or more values for said virtual column according to said virtual column expression.
A non-transitory computer-readable storage medium storing instructions for evaluating queries, the instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform:
- receiving one or more database commands that request to define a virtual column that contains results of a virtual column expression, said one or more database commands specifying:
- a name for said virtual column;
- said virtual column expression;
- and
- that said virtual column is an index key for an index that indexes data values contained in said virtual column;
- in response to receiving said one or more database commands, generating database metadata that:
- defines said virtual column as a named virtual column having said name;
- defines said virtual column as containing the results of said virtual column expression;
- and
- defines said virtual column as said index key for said index;
- receiving a query that references said virtual column by name;
- retrieving data from said index;
- generating results for the query based on said data from said index.
Dependencies are inferred from the claim text.