Selective database statistics recollection

• US 7,676,451 B2
• Filed: 05/03/2007
• Issued: 03/09/2010
• Est. Priority Date: 05/03/2007
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Burger, Louis, Vandervort, Frank Roderic
• Primary Examiner(s): Pham; Khanh B
• Assistant Examiner(s): CHEEMA, AZAM M
• Application Number:
  US11/743,897
• Publication Number:
  US 20080275840A1
• Time in Patent Office: 1,041 Days
• Field of Search: None
• US Class Current: 707/687
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

Abstract

The subject mater herein relates to database management and, more particularly, to selective database statistics recollection. Various embodiments provide one or more of system, methods, and software that identify database column and index states when statistics become stale to a point where query execution plans against respective columns and indexes fail to be efficient. These and other embodiments provide a way to update column and index statistics only when the statistics are determined to be stale. As a result, adverse database performance as a result of needless updating of database statistics is mitigated.

Claims

1. A database management system comprising:

a processor;

a computer readable media storing;

a data dictionary including rows of metadata, the metadata of each row to describe one or more of a column of a database table and a set of two or more columns forming an index stored in the database management system, wherein the metadata of at least a subset of the data dictionary rows includes;

a stale flag column implemented in a database that is to be set to true by a process that executes on a machine when the metadata of a row is determined to be out of date by the process; and

instructions defining database management system processes executable by the processor, the database management system processes including a stale flag process executable by the processor to set the stale flag to true when the metadata of a row of metadata is determined to be out of date; and

wherein the stale flag process, when preparing data to be inserted as new rows to one or more tables;

writes the data into temporary table;

collects statistics on each target table column of data in the temporary table that corresponds to a table column and sets of columns forming indexes for which statistics are collected for and stored in the data dictionary; and

sets the stale flag column to true for each column and index for which the temporary table statistics, when compared with statistics of the target column or index, indicate that the target column or index statistics should be updated.

2. The database management system of claim 1, wherein the stale flag process is part of a bulk load utility of the database management system.

3. The database management system of claim 1, wherein each row of the subset of data dictionary rows corresponds to a column or an index managed by the database management system.

4. The system of claim 1, wherein the metadata of the at least a subset of the data dictionary rows further includes:

a modified data counter column implemented in the database that is incremented by the stale flag process when a value of the column of a row or index is modified, deleted, or inserted.

5. The database management system of claim 4, wherein the stale flag process, before committing a transaction, executes on the machine to:

update the modified data counter column value of each affected column or index by adding a number of data items of a column or index modified and a respective current modified data counter column value affected by the transaction; and

set the stale flag column of each data item affected by the transaction to true if the updated modified data column counter of a data item is at least a threshold percentage of a number of rows in a table in which the data item is stored.

6. The database management system of claim 5, wherein the database management system processes further include:

a selective statistics update process executable by the processor to;

update column and index statistics for only those columns and indexes for which the stale flag column is set to true; and

set the stale flag column to false and reset the modified data counter column value to zero for each column and index for which the statistics were successfully updated.

7. The database management system of claim 1, wherein the database management system processes further include:

a selective statistics update process executable by the processor to;

update column and index statistics for only those columns and indexes for which the stale flag column is set to true; and

set the stale flag column to false for each column and index for which the statistics were successfully updated.

8. A method of maintaining statistics in a database management system, the method comprising:

maintaining a modified data counter and a stale flag for each column and index within a data dictionary of a database management system for which statistics are maintained;

incrementing the modified data counter of a column or index for each insert, update, and delete that affects data in the respective column of the modified data counter;

setting the stale flag to true if the modified data counter reaches a threshold value;

storing the stale flag when set to true;

writing data to be inserted into the database management system into a temporary table;

collecting statistics on each target table column of data in the temporary table that corresponds to a table column and sets of columns forming indexes for which statistics are collected for and stored in the data dictionary of the database management system; and

setting the stale flag to true for each column and index for which the temporary table statistics, when compared with statistics of the target column or index, indicate that the target column or index statistics should be updated.

9. The method of claim 8, wherein the threshold value is a percentage of all rows of data of a table to which the respective column is a member of or a dataset of the respective index.

10. The method of claim 8, further comprising:

selectively updating column or index statistics upon receipt of a selective statistics update command by;

identifying columns and indexes having a stale flag set to true;

recollecting the statistics of the identified columns and indexes; and

setting the stale flag to false for each column and index for which the statistics were updated.

11. The method of claim 8, wherein the method is performed by a database bulk load utility.

12. The method of claim 8, wherein the database management system is a relational database management system.

13. The method of claim 8, wherein the column and index statistics are maintained in a data dictionary of the database management system.

14. A computer readable storage medium, with instructions thereon to cause a suitably configured computer system to:

write data into a temporary database table;

collect statistics on the temporary table, the statistics collected on each column in the temporary table that corresponds to a column of a table in a target database and sets of columns in the temporary table corresponding to sets of columns in the target database forming indexes for which statistics are collected for and stored in a data dictionary of the target database;

compare the temporary table statistics with statistics of one or more columns or indexes stored in the data dictionary of the target database the data is to be written to for storage to determine if the temporary table will cause the database statistics stored in the data dictionary to become out of date; and

write the temporary table data to the target database and designate the statistics of one or more columns or indexes stored in the data dictionary of the target database as out of date if the statistics comparing indicates the database statistics stored in the data dictionary are out of date with the addition of the temporary table data.

15. The computer readable storage medium of claim 14, wherein database statistics are out of date when execution plans developed by a database management system to service queries of the database are no longer efficient.

16. The computer readable storage medium of claim 14, with further instructions thereon to cause the computer system to:

selectively update column or index statistics of the database upon receipt of a selective statistics update command by;

recollecting the statistics of the columns and indexes having statistics designated as out of date; and

designated the statistics of each column and index for which the statistics were updated as current.

17. The computer readable storage medium of claim 14, with further instructions thereon to cause the computer system to:

maintain a modified data counter for each column and index within a database management system for which statistics are maintained for;

incrementing the modified data counter of a column or index for each insert, update, and delete that affects data in the respective column of the modified data counter; and

designating the statistics of a column or index as out of date if the modified data counter reaches a threshold value.

18. The computer readable storage medium of claim 17, wherein the threshold value is a percentage of all rows of data of a table to which the respective column is a member of or a dataset of the respective index.