System and Method for Optimizing Queries
AssigneeHewlett-Packard Development Company, L.P.
InventorsRoger Mitchell, Renu Varshneya, Awny K. Al-Omari
Life of this patent
Estimating the term…
Abstract
There is provided a computer-implemented method of optimizing a query. An exemplary method retrieves a subset of rows from a sample table based on a query for a table associated with the sample table. The query specifies the table. The sample table is generated in a previous optimization of the query. The sample table includes a scrambled, random selection of rows from the table. A cardinality of the table is determined based on the subset. A query plan is generated based on the query and the cardinality.
Claims20
A computer-implemented method of optimization, comprising:
- retrieving a subset of rows from a sample table based on a query for a table associated with the sample table, the sample table being generated in a previous optimization, the sample table comprising a scrambled, random selection of rows from the table;
- determining a cardinality of the table based on the subset;
- and
- generating a query plan based on the query and the cardinality.
A computer system for optimization in a database management system (DBMS), the computer system comprising:
- a processor that executes stored instructions;
- and
- a memory device that stores instructions and data, the memory device comprising:
- a sample table generated in a previous optimization, the sample table comprising a scrambled, random subset of the rows of a source table, wherein the source table is associated with the sample table;
- computer-implemented code that determines the sample table is to be replaced based on metadata and a specified threshold;
- computer-implemented code that deletes the sample table;
- computer-implemented code that generates a new sample table comprising:
- all columns of the sample table;
- and
- a new scrambled, random subset of rows from the source table;
- computer-implemented code that retrieves a subset of rows from the new sample table based on a query;
- computer-implemented code that determines a cardinality of the table based on the subset;
- and
- computer-implemented code that generates a query plan based on the query and the cardinality.
A tangible, machine-readable medium that stores machine-readable instructions executable by a processor for optimization in a database management system (DBMS), the tangible, machine-readable medium comprising:
- a sample table generated in a previous optimization in the DBMS, the sample table comprising a scrambled, random subset of the rows of a source table associated with the sample table;
- machine-readable instructions that, when executed by the processor, determines the sample table is to be replaced based on a specified threshold and metadata;
- machine-readable instructions that, when executed by the processor, deletes the sample table;
- machine-readable instructions that, when executed by the processor, generates a new sample table comprising:
- all columns of the sample table;
- and
- a scrambled, random selection of rows from the source table;
- machine-readable instructions that, when executed by the processor, retrieves a subset of rows from the new sample table based on a query;
- machine-readable instructions that, when executed by the processor, determines a cardinality of the table based on the subset;
- and
- machine-readable instructions that, when executed by the processor, generates a query plan based on the query and the cardinality.
Dependencies are inferred from the claim text.