Back to results
US 2019/0197160 A1Published application
Techniques For Pushing Joins into Union All Views
AssigneeTeradata US, Inc.
InventorsAngela Maduko, Nelly Korenevsky
Life of this patent
Estimating the term…
Abstract
A query with a UNION ALL (UA) view is detected by a query optimizer. A query execution plan and cost for the query is obtained. The query is rewritten to push aggregates of the original query into the view. A query execution plan is generated for the rewritten query and a cost for executing the rewritten query is obtained. The lowest cost execution plan is selected for execution by a database engine of a database.
Claims20
A method, comprising:
- receiving a query and generating a query execution plan for the query with a first cost of executing the query execution plan;
- rewriting the query as a rewritten query by pushing aggregates of the query into a UNION ALL (UA) view;
- generating a second query execution plan with a second cost of executing the rewritten query;
- and
- selecting one of:
- the query and the rewritten query as a selected query for execution based on a lower cost associated with the first cost and the second cost.
3 more elements
The method of claim, wherein rewriting further includes pushing the aggregates into a derived table within the rewritten query.
A method, comprising:
- rewriting a query into a rewritten query that pushes aggregates into a UNION ALL (UA) view within the rewritten query;
- generating a first query execution plan for the query by hiding or masking the aggregates being pushed in the rewritten query during the generation;
- obtaining a first plan cost for the first query execution plan;
- and
- revealing the aggregates being pushed from the rewritten query for generating a second query execution plan of the rewritten query;
- accumulating costs during the generating of the second query execution plan and terminating the generating and selecting the query as a selected query for execution as soon as the costs exceed the first plan cost;
- and
- selecting the rewritten query as the selected query for execution with the second query execution plan when the second query execution plan is finished being generated and a final cost representing that the accumulating costs is lower than the first plan cost.
5 more elements
A system, comprising:
- a data warehouse including:
- a query optimizer;
- wherein the query optimizer is configured to:
- i) execute on at least one network node of the data warehouse, ii) rewrite an original query into a rewritten query that pushes aggregates of the original query into a UNION ALL view, iii) calculate a first cost to execute the original query, iv) calculate a second cost to execute the rewritten query, v) select a selected query for execution within the data warehouse as a lower cost of:
- the first cost and the second cost.
2 more elements
Dependencies are inferred from the claim text.