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

• US 20060036576A1
• Filed: 10/21/2005
• Published: 02/16/2006
• Est. Priority Date: 12/22/1999
• Status: Active Grant

• Current Assignee: Snowflake, Inc.
• Original Assignee: International Business Machines Corporation
• Inventors: Simmen, David E.
• Granted Patent: US 7,783,625 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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:

using data in one or more materialized query tables (MQTs), wherein the data is generated from the base tables, as statistics for determining an optimal execution plan for the query.

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 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 query.

13. The method of claim 12, further comprising retrieving data from the MQT relevant to the optimization of the 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 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 using data in one or more materialized query tables (MQTs), wherein the data is generated from the base tables, as statistics for determining an optimal execution plan for the query.

16. An article of manufacture embodying logic for performing 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:

using data in one or more materialized query tables (MQTs), wherein the data is generated from the base tables, as statistics for determining an optimal execution plan for the query.

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 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 query.

28. The article of claim 27, further comprising retrieving data from the MQT relevant to the optimization of the 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 query.