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.
Claims16
A computer-implemented method for use in evaluating queries, comprising:
- receiving a query that includes at least one predicate,
- wherein the at least one predicate references a virtual column,
- wherein a DBMS defines the virtual column as containing results of an expression, and
- wherein the virtual column comprises at least one data value computed from an expression;
- retrieving, from a statistics database, statistical data on the distribution of data values contained in the virtual column;
- estimating, based on the statistical data, a cost of executing said query.
A computer-implemented method for use in evaluating queries, comprising:
- receiving a query that includes 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;
- retrieving data from an index that indexes data values in the virtual column;
- generating results for the query based on the data.
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.
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.
A 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 the steps of:
- receiving a query that includes at least one predicate,
- wherein the at least one predicate references a virtual column,
- wherein a DBMS defines the virtual column as containing results of an expression, and
- wherein the virtual column comprises at least one data value computed from an expression;
- retrieving, from a statistics database, statistical data on the distribution of data values contained in the virtual column;
- estimating, based on the statistical data, a cost of executing said query.
A 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 the steps of:
- receiving a query that includes 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;
- retrieving data from an index that indexes data values in the virtual column;
- generating results for the query based on the data.
A 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.
A 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.
Dependencies are inferred from the claim text.