Systems and methods for statistics over complex objects

• US 20060230016A1
• Filed: 03/29/2005
• Published: 10/12/2006
• Est. Priority Date: 03/29/2005
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chen, Jianjun, Cunningham, Conor
• Application Number:
  US11/091,983
• Publication Number:
  US 20060230016A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

The subject invention relates to systems and methods that automatically create, update, and use statistics over complex objects within a database query processor. A system is provided that facilitates employment of statistics in connection with database optimization. The system includes a component that receives information relating to database performance and an optimization component that automatically identifies, from the information, a minimal set of statistics to employ in a query. A loader dynamically loads and employs the set of statistics during a query optimization process. The optimization component can employ the statistics over computed columns, whereby the columns can be computed from persisted and non-persisted data.

Claims

1. A system that facilitates employment of statistics in connection with database optimization, comprising:

a first component that receives information relating to database performance;

an optimization component that automatically identifies, from the information, a minimal set of statistics to employ in a query;

a second component that automatically creates or updates the set of statistics upon detection the statistics are absent or stale with respect to a column or a computed column; and

a loader that dynamically loads and employs the set of statistics during an optimization process.

2. The system of claim 1, the optimization component employs the statistics over computed columns.

3. The system of claim 2, at least one of the columns are computed from persisted or non-persisted data.

4. The system of claim 2, further comprising a component that matches scalar expressions to the computed columns.

5. The system of claim 1, the second component is employed to select query plans via a cardinality estimation.

6. The system of claim 1, the optimization component employs the statistics over scalar expressions.

7. The system of claim 1, the optimization component references scalar portions of complex or hierarchical objects and employs the statistics over the objects.

8. The system of claim 1, the optimization component references nested scalar portions of complex or hierarchical objects and employs the statistics over the objects.

9. The system of claim 1, the optimization component references derived scalar portions of complex or hierarchical objects and employs the statistics over the objects.

10. The system of claim 1, further comprising a component that normalizes references to complex or hierarchical objects to facilitate identifying scalars within the object.

11. The system of claim 1, further comprising a statistics loading component that loads statistics in a complex or hierarchical object as a function of hierarchical tracking of changes made to the objects.

12. The system of claim 11, further comprising a utility component that employs the statistics within a cost-based framework to determine optimized query plans over complex objects.

13. The system of claim 11, the statistics loading component adds new statistical metadata information to an existing metadata representation of statistics.

14. The system of claim 12, the statistics loading component bifurcates loading of statistics.

15. The system of claim 1, the optimization component re-computes cardinality upon addition of new statistics.

16. A computer readable medium having computer readable instructions stored thereon for implementing the components of claim 1.

17. A method for database query planning, comprising:

mapping multiple database expression forms into a singular expression form for a database;

processing statistics in view of the singular expression form; and

generating a query plan for the database.

18. The method of claim 17, further comprising generating a sub-column id for the singular expression form.

19. The method of claim 17, further comprising processing nested objects with the singular expression form.

20. A system to facilitate database planning operations, comprising:

means for processing information relating to database statistics;

means for generating a plan that automatically identifies a subset of the statistics to employ in a database query; and

means for loading the subset of statistics during a database optimization process.