Back to results
US 7,624,094 B2Granted
Automatic database statistics creation
AssigneeMicrosoft Corporation
InventorsNigel R. Ellis, Rodger N. Kline
Life of this patent
Estimating the term…
Abstract
A system for automatic statistics creation comprises a query optimizer which automatically generates statistics derived from data in a database and selects an executable procedure from a plurality of procedures that operate on data in a database using the automatically generated statistics. A counter is maintained of updates made to each statistic that has been automatically generated. If the counter breaches a threshold, the automatically generated statistic is removed from the database.
Claims11
A computer-implemented method of managing data in a database, comprising:
- selecting, using a query optimizer, an executable procedure from a plurality of procedures that operate on data in the database, wherein said query optimizer automatically:
- generates a statistic derived from the data that is operated upon by one of the plurality of procedures;
- and
- determines an optimal execution procedure based on said statistics, wherein the statistics are calculated automatically when the statistics are needed to determine the optimal execution procedure via a process comprising:
- accumulating names of columns to be processed by the execution procedure lacking statistics;
- determining whether the columns are of a type for which statistics may be generated, such that:
- in an event a selected column is of the type for which statistics may be generated, and the statistic does not already exist, automatically generating the statistic;
- and
- in an event the selected column is not of the type for which statistics may be generated, not generating the statistics, wherein types of columns for which statistics may not be generated comprise:
- read-only;
- and
- graph;
- selects the optimal executable procedure from the plurality of procedures based on the statistics including generated statistics;
- determines a threshold number of query modifications for the columns referenced by the generated statistic;
- maintains a modification counter of query modifications to the data referenced by the generated statistic;
- and
- recalculates the generated statistic when the modification counter reaches the threshold.
14 more elements
A computer-implemented method of managing data in a database, comprising:
- determining an optimal execution plan based on statistics, wherein the statistics are calculated automatically when the statistics are needed to determine the optimal execution plan via a process comprising:
- accumulating names of columns to be processed by the execution plan lacking statistics;
- determining whether the columns are of a type for which statistics may be generated, such that:
- in an event a selected column is of the type for which statistics may be generated, automatically generating the statistic;
- and
- in an event the selected column is not of the type for which statistics may be generated, not generating the statistic;
- wherein types of columns for which statistics may not be generated comprise:
- read-only;
- and
- graph;
- selecting the optimal execution plan based on the statistics including generated statistics;
- determining a threshold number of query modifications for an automatically generated statistic;
- maintaining a modification counter of the query modifications to the data for the automatically generated statistic;
- and
- deleting the automatically generated statistic when the modification counter reaches the threshold.
12 more elements
A system comprising:
- a processor;
- a memory operatively coupled to the processor, the memory containing instructions for execution by the processor;
- the instructions for execution by the processor comprising instructions to implement a method during generation of an execution plan, the method comprising:
- determining an optimal execution plan based on statistics, wherein the statistics are calculated automatically when the statistics are needed to determine the optimal execution plan via a process comprising:
- accumulating names of columns to be processed by the execution plan lacking statistics;
- determining whether the columns are of a type for which statistics may be generated, such that:
- in an event a selected column is of the type for which statistics may be generated, automatically generating the statistic;
- and
- in an event the selected column is not of the type for which statistics may be generated, not generating the statistic, wherein types of columns for which statistics may not be generated comprise:
- read-only;
- and
- graph;
- selecting the optimal execution plan based on the statistics including generated statistics;
- determining a threshold number of query modifications for an automatically generated statistic;
- maintaining a counter of the query modifications for the automatically generated statistic;
- and
- deleting the automatically generated statistic when the counter reaches the threshold.
14 more elements
Dependencies are inferred from the claim text.