1 / 37

Query Transformations

Learn about the power of query transformations in Oracle and how they can significantly improve query performance. Discover different types of transformations and their impact on query execution.

sherron
Download Presentation

Query Transformations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. They Don’t Call it the Optimizer for Nothing Steve Catmull UTOUG Fall Symposium 2012 Query Transformations

  2. Agenda • What is a Query Transformation • The Case for Query Transformations • Example Transformations • Summary

  3. About the Examples • Transformations are typically cost sensitive. • I will explain the idea behind the transformation • I will show you what the pattern looks like in the explain plan. • Version: 11.2.0.3

  4. What is a Transformation • They are designed to not effect your query results. • The Optimizer thinks there is a better way to execute a query by rewriting it. • Sometimes the rewrite is significant • It is often very clever • Mathematically sound • Adds/Removes/Changes operations

  5. The Case for Transformation • Oracle presented transformation techniques at VLDB in 2006. Their tests showed significant improvement.

  6. Types of Transformation • Cost-Based • The proposed transformation is added to the set of candidate plans it considers. • Those plans are costed and one is selected. • Rule-Based • When certain conditions exist, the optimization is always desirable. • ORDER BY Elimination • Join Elimination Source: Cost-Based Query Transformation in Oracle, VLDB 2006, Oracle USA Presentation

  7. Rule Based Transformations

  8. ORDER BY Elimination

  9. Join Elimination • Why join two tables if you don’t have to?

  10. Join Elimination • Change query to return only customer columns. • Why join to COUNTRIES?

  11. Join Elimination DDL • Primary / foreign key relationship between the tables • ENABALED / VALIDATE • DISABLED / RELY • Single column key Source: http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-query.html

  12. Cost-Based Transformations

  13. SubqueryUnnesting

  14. Subquery Pseudo Code • For every row in CUSTOMERS • Probe into SALES for sales by that customer ID who had >=2 quantity in the transaction.

  15. Un-Nesting Requirements • No HAVING in subquery • Subquery must be during or before WHERE clause in order of operations. • ….

  16. OR Expansion Source: https://blogs.oracle.com/optimizer/entry/or_expansion_transformation

  17. OR Expansion Notes • Sensitive to cardinalities / statistics • Enables two different NORMAL indexes on the same table to be accessed in the same SQL statement. Fancy trick. • Sightings in the wild depend a lot of your data and query patterns.

  18. Complex View Merging Source: http://jonathanlewis.wordpress.com/2007/03/08/transformation-and-optimisation/

  19. Complex View Merging Fail

  20. Predicate Pushdown

  21. Join Predicate Pushdown • Opens up joining to a view using nested loop index methods. • “Normally, a view cannot be joined with an index-based nested loop join” Source: https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle

  22. View Join Plan • Executes view independent of results of CUSTOMERS table.

  23. View Join Plan (Pushdown) • Each row in CUSTOMERS, send the CUST_ID to step 6 and execute view.

  24. Pushdown Takeaways • Tight correlation with nested loops. • Let it happen naturally. • Errant cardinality estimates may cause this to be used. Ways to correct • Use hint • Simplify query • More clever than it looks. • Shows up frequently in DSS systems.

  25. Coalescing Subqueries (ST)

  26. Coalescing Subqueries Plan • Transformed 2 exists into a single OR. • Only 1 hit against CUSTOMERS. • Un-nested query into a hash join • Added a predicate from constraints

  27. Materialized View Rewrite • You have users often group on common columns on a large table.

  28. MV Rewrite (baseline) selectprod_id, promo_id, count(distinct cust_id) AScust_id_cnt, sum(amount_sold) ASsold_amt fromsh.sales group byprod_id, promo_id

  29. Building an MV • If we build a materialized view to help speed up this query…

  30. MV Rewrite (MVScript) CREATEMATERIALIZEDVIEW SH.SALES_SMRY1_MV BUILDIMMEDIATE REFRESHCOMPLETE ENABLEQUERYREWRITE AS select prod_id,promo_id, count(distinctcust_id)AScust_id_cnt, sum(amount_sold)ASsold_amt from sh.sales groupby prod_id,promo_id

  31. MV Rewrite – Another Query `

  32. MV Rewrite Requirements

  33. Temp Transformation

  34. A Future Glimpse (uncorrelated subsumed subquery) • At 2009 VLDB, Oracle submitted a technique called uncorrelated subsumed subquery.

  35. Known Transformation List

  36. Summary • Oracle is adding more and more transformation rules • For complex DSS environments, transformers are you friend. It doesn’t mean you don’t fight. • Constraints can enable some transformations even when disabled and not validated.

More Related