Metadata manager for database query optimizer

• US 20040039729A1
• Filed: 08/20/2002
• Published: 02/26/2004
• Est. Priority Date: 08/20/2002
• Status: Active Grant

• Current Assignee: Alibaba Group Holding Ltd.
• Original Assignee: International Business Machines Corporation
• Inventors: Faunce, Michael S., Egan, Randy Lynn, Edwards, John Francis, Boger, Curtis Neal
• Granted Patent: US 6,996,556 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A metadata manager is used in a database management system to collect and maintain metadata associated with a database. Multiple optimizer instances are permitted to access the metadata maintained by the metadata manager, often eliminating the need for individual optimizer instances to retrieve and process metadata directly from the database. As such, the overhead associated with operations such as collecting, refining, retrieving and/or maintaining of metadata can be off-loaded from the optimizer instances, often accelerating individual cost estimation calculations by optimizer instances, facilitating reuse of metadata calculations and refinements, and improving metadata consistency between multiple related cost estimates.

Claims

1. An apparatus, comprising:

(a) a metadata manager configured to collect metadata associated with a database; and

(b) a plurality of optimizer instances, each optimizer instance configured to generate an execution plan for an associated query using metadata collected by the metadata manager.

2. The apparatus of claim 1, further comprising a plurality of data source caches, each data source cache configured to cache metadata associated with a data source from the database.

3. The apparatus of claim 2, wherein the metadata manager is configured to dynamically construct at least one data source cache in response to a request from an optimizer instance for metadata associated with a data source from the database.

4. The apparatus of claim 2, wherein at least one data source cache includes a data access descriptor configured to store metadata associated with an access path to the data source.

5. The apparatus of claim 4, wherein the data access descriptor includes metadata representative of at least one of a database table, a subset of columns from a database table, an index over a table, and a hash table.

6. The apparatus of claim 4, wherein the metadata manager is configured to dynamically construct the data access descriptor in response to a request from an optimizer instance for metadata associated with a data source from the database.

7. The apparatus of claim 2, further comprising a plurality of metadata contexts, each associated with an optimizer instance from the plurality of optimizer instances, and each configured to service access requests from an associated optimizer instance.

8. The apparatus of claim 7, wherein the metadata manager is configured to dynamically construct a metadata context in response to a request from an optimizer instance.

9. The apparatus of claim 2, wherein at least two metadata contexts are configured to access a common data source cache such that metadata stored in the common data source cache is accessible to the optimizer instances associated with the two metadata contexts.

10. The apparatus of claim 1, wherein the metadata manager is configured to refine the metadata collected from the database prior to providing the refined metadata to at least one optimizer instance.

11. The apparatus of claim 10, wherein the metadata manager is configured to provide the refined metadata to multiple optimizer instances.

12. The apparatus of claim 1, wherein the metadata manager is configured to generate refined metadata by processing an expression constructed by a first optimizer instance among the plurality of optimizer instances.

13. The apparatus of claim 12, wherein the metadata manager is configured to return refined metadata to the first optimizer instance by attaching the refined metadata to the expression constructed by the first optimizer instance.

14. The apparatus of claim 12, wherein the metadata manager is configured to cache the refined metadata and to return the cached refined metadata to a second optimizer instance among the plurality of optimizer instances in response to a request from the second optimizer instance.

15. The apparatus of claim 1, wherein the metadata manager is configured to process a first request for first metadata by calculating refined metadata from unrefined metadata and returning the refined metadata as the first metadata, and to process a second request for the first metadata by returning the refined metadata as the first metadata without recalculating the refined metadata.

16. The apparatus of claim 15, wherein the first and second requests are issued by the same optimizer instance.

17. The apparatus of claim 15, wherein the first and second requests are issued by different optimizer instances.

18. An apparatus, comprising:

(a) a processor; and

(b) program code configured to execute on the processor to collect metadata associated with a database, the program code further configured to supply the metadata to at least one query optimizer for use in generating an execution plan for a database query.

19. The apparatus of claim 18, further comprising a plurality of data source caches, each data source cache configured to cache metadata associated with a data source from the database, and wherein the program code is configured to dynamically construct at least one data source cache in response to a request from the query optimizer for metadata associated with a data source from the database.

20. The apparatus of claim 19, wherein the program code manager is further configured to dynamically construct a data access descriptor in connection with dynamically constructing a data source cache, wherein the data access descriptor is configured to store metadata associated with an access path to the data source.

21. The apparatus of claim 19, wherein the query optimizer includes a plurality of optimizer instances, the apparatus further comprising a plurality of metadata contexts, each associated with an optimizer instance from the plurality of optimizer instances, and each configured to service access requests from an associated optimizer instance, wherein the program code is configured to dynamically construct a metadata context in response to a request from an optimizer instance.

22. The apparatus of claim 21, wherein at least two metadata contexts are configured to access a common data source cache such that metadata stored in the common data source cache is accessible to the optimizer instances associated with the two metadata contexts.

23. The apparatus of claim 18, wherein the program code is configured to process a first request from the query optimizer for first metadata by calculating refined metadata from unrefined metadata and returning the refined metadata as the first metadata, and to process a second request from the query optimizer for the first metadata by returning the refined metadata as the first metadata without recalculating the refined metadata.

24. The apparatus of claim 23, wherein the query optimizer includes a plurality of optimizer instances, and wherein the first and second requests are from different optimizer instances.

25. A program product, comprising:

(a) program code configured to collect metadata associated with a database, the program code further configured to supply the metadata to at least one query optimizer for use in generating an execution plan for a database query; and

(b) a signal bearing medium bearing the program code.

26. The program product of claim 25, wherein the signal bearing medium includes at least one of a transmission medium and a recordable medium.

27. A method of managing metadata for a database, the method comprising:

(a) collecting metadata associated with a database; and

(b) responding to a plurality of requests by at least one query optimizer by supplying the collected metadata thereto.

28. The method of claim 27, further comprising caching the metadata in a plurality of data source caches, each data source cache configured to cache metadata associated with a data source from the database.

29. The method of claim 28, further comprising dynamically constructing at least one data source cache in response to a request from the query optimizer.

30. The method of claim 28, wherein at least one data source cache includes a data access descriptor configured to store metadata associated with an access path to the data source.

31. The method of claim 30, further comprising dynamically constructing the data access descriptor in response to a request from the query optimizer for metadata associated with a data source from the database.

32. The method of claim 28, wherein the query optimizer includes a plurality of optimizer instances, the method further comprising dynamically constructing a plurality of metadata contexts in response to requests from the plurality of optimizer instances, each metadata context associated with an optimizer instance from the plurality of optimizer instances, and each configured to service access requests from an associated optimizer instance.

33. The method of claim 32, wherein at least two metadata contexts are configured to access a common data source cache such that metadata stored in the common data source cache is accessible to the optimizer instances associated with the two metadata contexts.

34. The method of claim 27, further comprising refining the metadata collected from the database prior to supplying the refined metadata in response to a request from the query optimizer.

35. The method of claim 34, wherein refining the metadata includes processing an expression constructed by the query optimizer.

36. The method of claim 27, further comprising:

(a) processing a first request for first metadata by calculating refined metadata from unrefined metadata and returning the refined metadata as the first metadata; and

(b) processing a second request for the first metadata by returning the refined metadata as the first metadata without recalculating the refined metadata.

37. A method of optimizing a database query, the method comprising:

(a) accessing a metadata manager to retrieve metadata associated with a database; and

(b) using the retrieved metadata to optimize a database query.

38. The method of claim 37, wherein the retrieved metadata comprises refined metadata, the method further comprising, in the metadata manager:

(a) collecting the unrefined metadata from the database; and

(b) calculating the refined metadata from unrefined metadata.

39. The method of claim 38, further comprising:

(a) caching the refined metadata in the metadata manager;

(b) accessing the metadata manager a second time to retrieve the cached refined metadata; and

(c) using the cached refined metadata to optimize a second database query.