Techniques for improving the performance of complex queries

• US 10,268,724 B2
• Filed: 09/30/2013
• Issued: 04/23/2019
• Est. Priority Date: 03/15/2013
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Korlapati, Rama Krishna, Sinclair, Paul, Au, Grace Kwan-On, Sarathy, Vijay
• Primary Examiner(s): Hershley, Mark E
• Application Number:
  US14/041,790
• Publication Number:
  US 20140280036A1
• Time in Patent Office: 2,031 Days
• Field of Search: 707718
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Techniques for improving complex database queries are provided. A determination is made whether to adopt a static or dynamic query execution plan based on metrics. When the dynamic query execution plan is used, a request fragment of the request is planned and the corresponding plan fragment is executed. The processed fragment provides feedback related to its processing to the remaining request and the process is repeated on the remaining request until the request is completed.

Claims

1. A method implemented and programmed within a non-transitory computer-readable storage medium and processed by a processor, the processor configured to execute the method, comprising:

determining, at the processor, whether to execute a request via a static plan generation or a dynamic plan generation, wherein determining comprises;

producing, at the processor, a static plan by using the static plan generation to evaluate costs and conditions of the static plan;

selecting, at the processor, to process one of the dynamic plan generation for the request and the static plan for the request, and wherein selecting to process the dynamic plan generation comprises processing statistical information from intermediate spools, and wherein no portion of the dynamic plan is generated prior to selection;

in response to selection of the dynamic plan generation;

sequentially fragmenting, at the processor, the request into a plurality of request fragments;

generating, at the processor, a first plan fragment corresponding to a first request fragment from the plurality of request fragments; and

executing, at the processor, the first plan fragment; and

for each other request fragment;

generating, at the processor, a corresponding plan fragment in response to execution of an immediately preceding plan fragment;

executing, at the processor, the corresponding plan fragment; and

providing, at the processor, feedback based on execution of the corresponding plan fragment to a remaining portion of the request, wherein execution of a last plan fragment completes the request.

2. The method of claim 1, wherein selecting comprises evaluating, at the processor, the costs, the conditions, and threshold values select to process the one of the static plan or the dynamic plan generation.

3. The method of claim 2, wherein selecting further comprises selecting the static plan for the request when the static plan is a low cost plan based on comparison of the costs with the threshold values or when no opportunities exist for using a dynamic plan for the request based on the conditions.

4. The method of claim 1, wherein fragmenting comprises evaluating the request for one or more of:

non-correlated scalar sub-queries, tables with single-row access based on unique indexes, single-row query blocks, spooled sub-queries, spooled views and derived tables, complex joins, and dynamic fragmentation of a join plan based on cost and confidence factors.

5. The method of claim 1, wherein fragmenting comprises generating a first request fragment as a sub-portion of the request representing a first request fragment.

6. The method of claim 1, wherein executing comprises applying feedback from the executed first plan fragment to the remaining request.

7. A method implemented and programmed within a non-transitory computer-readable storage medium and processed by a processor, the processor configured to execute the method, comprising:

evaluating, at the processor, a query to produce a dynamic query plan for a query optimizer by producing a static plan and evaluating costs and conditions of that static plan and determining that the dynamic query plan is optimal for processing the query, and wherein when determining that the dynamic query plan is optimal for processing the query further comprises processing statistical information from intermediate spools as feedback details, and wherein no portion of the dynamic plan is produced prior to determining that the dynamic query plan is optimal for processing the query;

iterating, at the processor, the query to produce a plurality of request fragments, and generating a plan fragment for each request fragment, wherein each plan fragment is generated in response to execution of a prior plan fragment; and

sequentially passing, at the processor, the feedback details from executing each plan fragment to the remaining query.

8. The method of claim 7, wherein evaluating further comprises processing components of the query based on predefined conditions detected.

9. The method of claim 8, wherein iterating parsing further comprises detecting within the components one or more of:

non-correlated scalar sub-queries, tables with single-row access based on unique indexes, single-row query blocks, spooled sub-queries, spooled views and derived tables, complex joins, and dynamic fragmentation of a join plan based on cost and confidence factors.

10. The method of claim 9, wherein detecting comprises identifying the non-correlated scalar sub-queries as those components of the query that produce a zero or a singlerow result with a single scalar value.

11. The method of claim 9, wherein detecting further comprises identifying the tables with single-row access via a unique primary index or a unique secondary index as those components having an access path for a table accessed via unique index to assure a single row or no row.

12. The method of claim 9, wherein detecting further comprises identifying the single-row query blocks as those components having a derived table of query blocks that produce a single-row or zero-row result.

13. The method of claim 9, wherein detecting further comprises identifying the spooled sub-queries as those components for a sub-query that are not folded into an outer query.

14. The method of claim 9, wherein detecting further includes comprises the spooled derived tables or views as those components for a derived table or view that are not folded into an outer query.

15. The method of claim 9, wherein detecting further comprises identifying the complex joins as those components associated with outer joins or nested joins.

16. The method of claim 9, wherein detecting further comprises identifying the dynamic fragmentation within the query block as those components having multiple joins that alter a confidence in an estimated cardinality.

17. A system, comprising:

memory configured with a query plan selector that processes on a processor of a network; and

the memory configured with a dynamic query plan manager that processes on the processor of the network;

wherein the query plan selector is configured to initially generate a static plan for a request and make a determination as to whether to use the static plan with the request or to generate and to use a dynamic plan for the request based on evaluation of costs and conditions for that static plan, wherein no portion of the dynamic plan is generated prior to selection of the dynamic plan, and wherein the dynamic query plan manager is configured to process components of the request and sequentially generate request fragments and their corresponding plan fragments, wherein execution of each plan fragment provides feedback to the remaining request and processing statistical information from intermediate spools as the feedback fragment, and wherein each plan fragment is generated in response to execution of a prior plan fragment.

18. The system of claim 17, wherein the query plan selector utilizes the costs, the conditions, and threshold values to make the determination.