Selectivity-based optimized-query-plan caching

• US 8,224,806 B2
• Filed: 07/31/2009
• Issued: 07/17/2012
• Est. Priority Date: 07/31/2009
• Status: Expired due to Fees

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Al-Omari, Awny K., Reyes, Tom C., Wehrmeister, Robert M., Ezzat, Ahmed K., Chen, QiFan
• Primary Examiner(s): Mahmoudi, Tony
• Assistant Examiner(s): NGUYEN, MERILYN P
• Application Number:
  US12/533,365
• Publication Number:
  US 20110029508A1
• Time in Patent Office: 1,082 Days
• Field of Search: 707/713, 707/715, 707/718
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Optimized query plans are partitioned into equivalence groups that each include equivalence classes. Each equivalence group corresponds to a particular compiled, normalized, and parameterized query plan prior to optimization. Each equivalence class within an equivalence group corresponds to a different query plan corresponding to the particular compiled, normalized, and parameterized query plan represented by the equivalence group that has been optimized with respect to the selectivity of one or more predicate clauses of the query that is compiled to produce the particular compiled, normalized, and parameterized query plan. Optimized query plans are cached according to their respective equivalence groups and equivalence classes. When a query, similar to a query already compiled, optimized, and cached, is subsequently received and compiled, a selectivity for a predicate of the compiled query is computed, allowing the database management system to retrieve a cached query plan optimized for a similar query with similar selectivity.

Claims

1. An optimized-query-plan caching subsystem within a query-processing computer system, the optimized-query-plan caching subsystem comprising:

an optimized-query-plan cache contained in electronic memory of a computer system or distributed across one or more electronic memories and mass-storage devices within a computer system; and

a caching component that executes within the query-processing computer system and that stores optimized query plans in the optimized-query-plan cache in association with a representation of an equivalence group and in association with a representation of an equivalence class contained within the equivalence group, and that retrieves optimized query plans from the optimized-query-plan cache by;

receiving a query plan and a predicate selectivity computed for the query plan;

determining whether an equivalence group for the query plan is stored in the optimized-query-plan cache;

when an equivalence group for the query plan is stored in the optimized-query-plan cache, traversing equivalence-class representations of equivalence class contained in the equivalence group to identify an equivalence class that corresponds to the received predicate selectivity computed for the query plan; and

when an equivalence class that corresponds to the received predicate selectivity is identified, retrieving an optimized query plan stored in association with the equivalence class that has been identified,

wherein an equivalence class corresponds to the received predicate selectivity when;

the received predicate selectivity is equal to a single predicate selectivity that defines the equivalence class;

the received predicate selectivity is greater than or equal to a low predicate selectivity and less than or equal to a high predicate selectivity that together define the equivalence class;

the received predicate selectivity is within a threshold distance of a single predicate selectivity that defines the equivalence class;

or

the received predicate selectivity is within a threshold distance of a low predicate selectivity or a high predicate selectivity that together define the equivalence class.

2. The optimized-query-plan caching subsystem of claim 1 wherein each equivalence-group representation stored in the optimized-query-plan cache represents a normalized and parameterized query plan.

3. The optimized-query-plan caching subsystem of claim 2 wherein each equivalence-class representation stored in the optimized-query-plan cache represents one optimized query plan produced by optimizing the normalized and parameterized query plan represented by the equivalence group that contains the equivalence class following replacement of parameters with literal values.

4. The optimized-query-plan caching subsystem of claim 3 wherein each equivalence-class representation stored in the optimized-query-plan cache is defined by a predicate selectivity or a range of predicate selectivities computed for queries that, when normalized and parameterized, are equivalent to the normalized and parameterized query plan represented by the equivalence group.

5. The optimized-query-plan caching subsystem of claim 4 wherein the caching component stores an optimized query into the optimized-query-plan cache by:

receiving the optimized query plan and a predicate selectivity computed for the optimized query plan;

determining whether an equivalence group for the optimized query plan is stored in the optimized-query-plan cache;

when an equivalence group for the optimized query plan is not stored in the optimized-query-plan cache, creating an equivalence group for the optimized query plan, creating a first equivalence class for the optimized query plan defined by the predicate selectivity computed for the optimized query plan, and storing the optimized query plan in the first equivalence class;

when an equivalence group for the optimized query plan is stored in the optimized-query-plan cache, traversing equivalence-class representations of equivalence classes contained in the equivalence group to identify an equivalence class that corresponds to the received predicate selectivity and, when an equivalence class that corresponds to the received predicate selectivity is not identified, adding a new equivalence class to the equivalence group and storing the optimized query plan in the new equivalence class.

6. The optimized-query-plan caching subsystem of claim 5 wherein an equivalence class corresponds to the received predicate selectivity when:

the received predicate selectivity is equal to a single predicate selectivity that defines the equivalence class;

the received predicate selectivity is greater than or equal to a low predicate selectivity and less than or equal to a high predicate selectivity that together define the equivalence class;

the received predicate selectivity is within a threshold distance of a single predicate selectivity that defines the equivalence class;

or

the received predicate selectivity is within a threshold distance of a low predicate selectivity or a high predicate selectivity that together define the equivalence class.

7. A method for optimized-query-plan caching carried out within a query-processing computer system that includes an optimized-query-plan cache contained in electronic memory of the computer system or distributed across one or more electronic memories and mass-storage devices within the computer system, the optimized-query-plan caching method comprising:

storing, by a caching component that executes within the query-processing computer system, optimized query plans in the optimized-query-plan cache in association with a representation of an equivalence group and in association with a representation of an equivalence class contained within the equivalence group, by, for an optimized query plan;

receiving the optimized query plan and a predicate selectivity computed for the optimized query plan;

determining whether an equivalence group for the optimized query plan is stored in the optimized-query-plan cache;

when an equivalence group for the optimized query plan is not stored in the optimized-query-plan cache, creating an equivalence group for the optimized query plan, creating a first equivalence class for the optimized query plan defined by the predicate selectivity computed for the optimized query plan, and storing the optimized query plan in the first equivalence class;

when an equivalence group for the optimized query plan is stored in the optimized-query-plan cache, traversing equivalence-class representations of equivalence classes contained in the equivalence group to identify an equivalence class that corresponds to the received predicate selectivity, and when an equivalence class that corresponds to the received predicate selectivity is not identified, adding a new equivalence class to the equivalence group and storing the optimized query plan in the new equivalence class; and

retrieving, by the caching component, optimized query plans from the optimized-query-plan cache,

wherein an equivalence class corresponds to the received predicate selectivity when;

the received predicate selectivity is equal to a single predicate selectivity that defines the equivalence class;

the received predicate selectivity is greater than or equal to a low predicate selectivity and less than or equal to a high predicate selectivity that together define the equivalence class;

the received predicate selectivity is within a threshold distance of a single predicate selectivity that defines the equivalence class;

or

the received predicate selectivity is within a threshold distance of a low predicate selectivity or a high predicate selectivity that together define the equivalence class.

8. The method of claim 7 wherein each equivalence-group representation stored in the optimized-query-plan cache represents a normalized and parameterized query plan.

9. The method of claim 8 wherein each equivalence-class representation stored in the optimized-query-plan cache represents one optimized query plan produced by optimizing the normalized and parameterized query plan represented by the equivalence group that contains the equivalence class following replacement of parameters with literal values.

10. The method of claim 9 wherein each equivalence-class representation stored in the optimized-query-plan cache is defined by a predicate selectivity or a range of predicate selectivities computed for queries that, when normalized and parameterized, are equivalent to the normalized and parameterized query plan represented by the equivalence group.

11. The method of claim 10 wherein the caching component retrieves an optimized query plan from the optimized-query-plan cache by:

receiving a query plan and predicate selectivity computed for the query plan;

determining whether an equivalence group for the query plan is stored in the optimized-query-plan cache;

when an equivalence group for the query plan is stored in the optimized-query-plan cache, traversing equivalence-class representations of equivalence class contained in the equivalence group to identify an equivalence class that corresponds to the received predicate selectivity; and

when an equivalence class that corresponds to the received predicate selectivity is identified, retrieving an optimized query plan stored in association with the identified equivalence class.

12. The method of claim 11 wherein an equivalence class corresponds to the received predicate selectivity when:

the received predicate selectivity is equal to a single predicate selectivity that defines the equivalence class;

the received predicate selectivity is greater than or equal to a low predicate selectivity and less than or equal to a high predicate selectivity that together define the equivalence class;

the received predicate selectivity is within a threshold distance of a single predicate selectivity that defines the equivalence class;

or

the received predicate selectivity is within a threshold distance of a low predicate selectivity or a high predicate selectivity that together define the equivalence class.

13. A query-processing computer system comprising:

a query-receiving component that receives queries for processing;

a query-compiler component that generates a query plan for each received query;

a query-optimizer component that, when a suitable optimized cached query plan is available for a query plan, retrieves the optimized cached query plan for execution and otherwise optimizes the query plan;

a query-plan execution component that executes optimized query plans;

an optimized-query-plan cache contained in electronic memory or distributed across one or more electronic memories and mass-storage devices within the query-processing computer system; and

a caching component that executes within the query-processing computer system and that stores optimized query plans in the optimized-query-plan cache in association with a representation of an equivalence group and in association with a representation of an equivalence class contained within the equivalence group, and that retrieves optimized query plans from the optimized-query-plan cache by;

receiving a query plan and a predicate selectivity computed for the query plan;

determining whether an equivalence group for the query plan is stored in the optimized-query-plan cache;

when an equivalence group for the query plan is stored in the optimized-query-plan cache, traversing equivalence-class representations of equivalence class contained in the equivalence group to identify an equivalence class that corresponds to the received predicate selectivity computed for the query plan; and

when an equivalence class that corresponds to the received predicate selectivity is identified, retrieving an optimized query plan stored in association with the equivalence class that has been identified,

wherein an equivalence class corresponds to the received predicate selectivity when;

the received predicate selectivity is equal to a single predicate selectivity that defines the equivalence class;

the received predicate selectivity is greater than or equal to a low predicate selectivity and less than or equal to a high predicate selectivity that together define the equivalence class;

the received predicate selectivity is within a threshold distance of a single predicate selectivity that defines the equivalence class;

or

the received predicate selectivity is within a threshold distance of a low predicate selectivity or a high predicate selectivity that together define the equivalence class.

14. The optimized-query-plan caching subsystem of claim 13 wherein each equivalence-group representation stored in the optimized-query-plan cache represents a normalized and parameterized query plan.

15. The optimized-query-plan caching subsystem of claim 14 wherein each equivalence-class representation stored in the optimized-query-plan cache represents one optimized query plan produced by optimizing the normalized and parameterized query plan represented by the equivalence group that contains the equivalence class following replacement of parameters with literal values.

16. The optimized-query-plan caching subsystem of claim 15 wherein each equivalence-class representation stored in the optimized-query-plan cache is defined by a predicate selectivity or a range of predicate selectivities computed for queries that, when normalized and parameterized, are equivalent to the normalized and parameterized query plan represented by the equivalence group.