Sampling statistics in a database system

• US 20040059743A1
• Filed: 09/25/2002
• Published: 03/25/2004
• Est. Priority Date: 09/25/2002
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Burger, Louis M.
• Granted Patent: US 7,778,996 B2
• Time in Patent Office: Days
• US Class Current: 707/101
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

Abstract

A database system has the ability to perform sampled statistics collection, in which collection of statistics of a table is based on a sample (less than all rows of the table). The sample size is adjusted as the table is scanned in response to detecting a predetermined characteristic of the table (e.g., skewed data values are detected).

Claims

1. A method for use in a database system, comprising:

scanning a sample of a table, the sample containing less than all rows of the table;

determining a characteristic of data read by the scanning; and

adjusting a size of the sample as the table is scanned based on the determined characteristic; and

collecting statistics based on the sample.

2. The method of claim 1, wherein determining the characteristic comprises determining whether a data value is skewed.

3. The method of claim 2, wherein determining whether the data value is skewed comprises determining if a frequency of the data value is greater than a predetermined threshold.

4. The method of claim 2, wherein adjusting the size of the sample comprises increasing the size of the sample in response to detecting a data value is skewed.

5. The method of claim 4, wherein adjusting the size of the sample comprises successively increasing the size of the sample in response to plural instances of detecting skew.

6. The method of claim 1, wherein scanning the sample comprises reading one block, skipping N blocks, and then repeating the reading and skipping until complete.

7. The method of claim 6, wherein adjusting the size of the sample comprises adjusting a value of N.

8. The method of claim 7, wherein determining the characteristic comprises determining whether a data value is skewed

9. The method of claim 1, wherein collecting the statistics comprises building a histogram.

10. The method of claim 1, further comprising storing the table on plural storage modules that are managed by respective access modules, wherein the scanning, determining, adjusting, and collecting are performed by each of the access modules in parallel.

11. An article comprising at least one storage medium containing instructions that when executed cause a system to:

collect statistics of an attribute of a table based on a sample of the table, the sample having less than all rows of the table;

detect for a predetermined characteristic of the table; and

vary a size of the sample during statistics collection in response to detecting the predetermined characteristic.

12. The article of claim 11, wherein detecting for the predetermined characteristic comprises detecting for data skew.

13. The article of claim 12, wherein varying the size of the sample comprises increasing the size of the sample in response to detecting data skew.

14. The article of claim 13, wherein detecting for data skew comprises determining if a number of duplicates of a data value exceeds a predetermined threshold.

15. The article of claim 11, wherein the instructions when executed cause the system to skip rows of data to obtain the samples less than all rows of the table.

16. The article of claim 11, wherein the instructions when executed cause the system to read a block of data, skip N blocks, and repeat the reading and skipping until completed to obtain the sample less than all rows of the table.

17. A database system comprising:

a storage to store a table; and

a controller to;

read a sample of the table that is less than all rows of the table;

collect statistics based on the sample; and

adjust a size of the sample based on a predetermined characteristic of the table.

18. The database system of claim 17, the controller to collect statistics of an attribute of the table, the attribute being sorted by some predetermined algorithm.

19. The database system of claim 18, wherein the predetermined algorithm comprises a hash code.

20. The database system of claim 17, wherein the predetermined characteristic comprises data skew.

21. The database system of claim 20, the controller to increase the size of the sample in response to detecting data skew.

22. The database system of claim 17, wherein the storage contains multiple storage modules, and the controller comprises plural access modules to manage access of the storage modules, wherein each of the access modules performs the reading, collecting, and adjusting in parallel.