Using data in materialized query tables as a source for query optimization statistics

• US 7,783,625 B2
• Filed: 10/21/2005
• Issued: 08/24/2010
• Est. Priority Date: 12/22/1999
• Status: Active Grant

• Current Assignee: Snowflake, Inc.
• Original Assignee: International Business Machines Corporation
• Inventors: Simmen, David E.
• Primary Examiner(s): Mofiz; Apu M
• Assistant Examiner(s): Nguyen; Cindy
• Application Number:
  US11/256,440
• Publication Number:
  US 20060036576A1
• Time in Patent Office: 1,768 Days
• Field of Search: 707/2, 707/3
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Data in materialized query tables (MQTs) are used as statistics for determining the optimal execution plan for a query. When an MQT is defined, it is examined to determine whether its data provides statistics for determining an optimal execution plan for a query. If so, then the MQT is identified, in the RDBMS, as a source for statistics. Information needed to exploit the MQT data as statistics is cataloged in the RDBMS. This information includes a characterization of the type of statistics provided by the MQT, the table and column distributions represented by those statistics, and a query for later retrieving relevant data from the MQT during the query optimization process. When a query is accepted for execution, the cataloged relevant information about MQTs is examined to determine whether an MQT exists that provides statistics relevant to optimization of the query. If such an MQT exists, then the relevant data is retrieved from the MQT using the cataloged query. Using the retrieved statistics, an optimal execution plan may be determined for the query.

Claims

1. A method of optimizing execution of a query that accesses data stored in one or more base tables in a database of a computer system, comprising:

performing a first query that accesses the data stored in the base tables, in order to create at least one materialized query table (MQT);

using data stored in the MQT as statistics data for determining an optimal execution plan for a second query that accesses the data stored in the base tables; and

performing the second query using the optimal execution plan to access the data stored in the base tables in the database for presentation to a user.

2. The method of claim 1, wherein the statistics comprise table cardinality.

3. The method of claim 1, wherein the statistics comprise column distribution statistics for one or more columns.

4. The method of claim 3, wherein the column distribution statistics for one or more columns comprise a number of distinct values, high values, low values, cumulative distribution functions, or other types of column distribution statistic that can be computed by a query expression.

5. The method of claim 1, further comprising identifying the MQT as a source for statistics when the MQT is defined.

6. The method of claim 5, further comprising creating an index for the MQT when the MQT data is identified as a source for statistics, in order to expedite retrieving the statistics.

7. The method of claim 5, wherein the identifying step further comprises cataloging relevant information about the MQT as the source of statistics.

8. The method of claim 7, further comprising cataloging a query for retrieving data in the MQT when the MQT is identified as a source for the statistics.

9. The method of claim 7, further comprising cataloging a characterization of a type of statistics provided by the MQT when the MQT is identified as a source for the statistics.

10. The method of claim 7, further comprising cataloging table and columns distributions provided by the MQT when the MQT is identified as a source for the statistics.

11. The method of claim 7, further comprising exploiting the cataloged relevant information about the MQT to determine the optimal execution plan for the second query.

12. The method of claim 11, further comprising examining the cataloged relevant information about the MQT to determine whether the MQT provides statistics relevant to optimization of the second query.

13. The method of claim 12, further comprising retrieving data from the MQT relevant to the optimization of the second query using the cataloged query.

14. The method of claim 13, wherein the data is retrieved from the MQT when the time needed to retrieve the data is negligible compared to the time needed to optimize the second query.

15. A computer-implemented apparatus for optimizing execution of a query that accesses data stored in one or more base tables in a database of a computer system, comprising:

(a) a computer system having a data storage device coupled thereto, the data storage device storing the database; and

(b) logic, performed by the computer system, for performing a first query that accesses the data stored in the base tables, in order to create at least one materialized query table (MQT);

for using data stored in the MQT as statistics data for determining an optimal execution plan for a second query that accesses the data stored in the base tables; and

for performing the second query using the optimal execution plan to access the data stored in the base tables in the database for presentation to a user.

16. An article of manufacture comprising a program storage device for storing instructions that, when read and executed by a computer system, result in the computer system performing a method of optimizing execution of a query that accesses data stored in one or more base tables in a database of the computer system, comprising:

performing a first query that accesses the data stored in the base tables, in order to create at least one materialized query table (MQT);

using data stored in the MQT as statistics data for determining an optimal execution plan for a second query that accesses the data stored in the base tables; and

performing the second query using the optimal execution plan to access the data stored in the base tables in the database for presentation to a user.

17. The article of claim 16, wherein the statistics comprise table cardinality.

18. The article of claim 16, wherein the statistics comprise column distribution statistics for one or more columns.

19. The article of claim 18, wherein the column distribution statistics for one or more columns comprise a number of distinct values, high values, low values, cumulative distribution functions, or other types of column distribution statistic that can be computed by a query expression.

20. The article of claim 16, further comprising identifying the MQT as a source for statistics when the MQT is defined.

21. The article of claim 20, further comprising creating an index for the MQT when the MQT data is identified as a source for statistics, in order to expedite retrieving the statistics.

22. The article of claim 20, wherein the identifying step further comprises cataloging relevant information about the MQT as the source of statistics.

23. The article of claim 22, further comprising cataloging a query for retrieving data in the MQT when the MQT is identified as a source for the statistics.

24. The article of claim 22, further comprising cataloging a characterization of a type of statistics provided by the MQT when the MQT is identified as a source for the statistics.

25. The article of claim 22, further comprising cataloging table and columns distributions provided by the MQT when the MQT is identified as a source for the statistics.

26. The article of claim 22, further comprising exploiting the cataloged relevant information about the MQT to determine the optimal execution plan for the second query.

27. The article of claim 26, further comprising examining the cataloged relevant information about the MQT to determine whether the MQT provides statistics relevant to optimization of the second query.

28. The article of claim 27, further comprising retrieving data from the MQT relevant to the optimization of the second query using the cataloged query.

29. The article of claim 28, wherein the data is retrieved from the MQT when the time needed to retrieve the data is negligible compared to the time needed to optimize the second query.