TECHNIQUES FOR IMPROVING THE PERFORMANCE OF COMPLEX QUERIES

• US 20140280036A1
• Filed: 09/30/2013
• Published: 09/18/2014
• 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
• Granted Patent: US 10,268,724 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• 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;

sequentially fragmenting, at the processor, the request into request fragments when the dynamic plan generation is selected; and

providing, at the processor, plan fragments for each request fragment to execute the request.

2. The method of claim 1, wherein determining further includes evaluating costs, conditions, and threshold values to determine whether to use the static plan generation or the dynamic plan generation.

3. The method of claim 2, wherein evaluating further includes generating a static plan for the request to acquire the costs and conditions.

4. The method of claim 3, wherein generating further includes using 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.

5. The method of claim 1, wherein fragmenting further includes 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.

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

7. The method of claim 6, wherein generating further includes generating a first plan fragment for the first request fragment.

8. The method of claim 7, wherein generating further includes executing the first plan fragment.

9. The method of claim 8, wherein executing further includes applying feedback from the executed first plan fragment to the remaining request.

10. The method of claim 9, wherein applying further includes iterating to a next request fragment, building a corresponding plan fragment, executing the plan fragment, and applying feedback from the executed next plan fragment to the remaining request until the request is completely processed.

11. 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;

iterating, at the processor, the query to produce a plurality of request fragments, generating a plan fragment for each request fragment; and

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

12. The method of claim 11, wherein evaluating further includes processing components of the query based on predefined conditions detected.

13. The method of claim 12, wherein the parsing further includes 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.

14. The method of claim 13, wherein detecting further includes identifying the non-correlated scalar sub-queries as those components of the query that produce a zero or a single-row result with a single scalar value.

15. The method of claim 13, wherein detecting further includes 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.

16. The method of claim 13, wherein detecting further includes 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.

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

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

19. The method of claim 13, wherein detecting further includes identifying the complex joins as those components associated with outer joins or nested joins.

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

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

21. 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 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, 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 the execution of each plan fragment provides feedback to the remaining request.