Forum Stats

  • 3,873,746 Users
  • 2,266,637 Discussions


Join predicate pushing



  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Apr 19, 2018 10:44AM
    Sekar_BLUE4EVER wrote:Thanks for the reply Jonathan.Regarding picking up hash join I think machines are learning As you posted I changed the join order to A->B->D but rather than rewriting the query I copied the OUTLINE_DATA from the plan as a hint and changed a line to
     LEADING(@SEL$B8F0B828_2 "ENT"@SEL$B8F0B828_2 "RNR"@SEL$B8F0B828_2 "OTC"@SEL$B8F0B828_2 "TSS"@SEL$B8F0B828_2)
     LEADING(@SEL$B8F0B828_2 "ENT"@SEL$B8F0B828_2 "OTC"@SEL$B8F0B828_2 "RNR"@SEL$B8F0B828_2 "TSS"@SEL$B8F0B828_2)
    I did this as I was worried that rewriting the query would force the join order for first part of the CONCAT operation as well. This would fix the issue but i am curious to know a couple of things1. Now I understand why oracle decides to push down the join as there is a order condition and the stats are bad Is there is hint which would stop the join operation from being pushed down ? I am looking to understand this this so I can force it IF needed in future.2. Is there a way to find out the expanded SQL text with the union operation after the "OR expansion " ? I tried the dbms_sql2.expand_sql_text from your blog but it doesnt give the intenally optimised query with union operation .3. Does sql profile do exactly the same thing as hinting the entire BASELINE data from the plan we choose ? If so I think hinting BASELINE data isnt exactly fool proof For example I observed that OR_expansion doesnt apply the same expansion even after hinting it from baseline .Sometimes it gets expanded as 12 queries and sometimes as 20 queries .(Yeah the queries are that complex with OR after OR after OR )Thanks for sharing your valuable insights

    The query rewrite I was suggesting would mean that the optimizer would HAVE to join A->B first and then do a concat, so it couldn't change the join order for the first part of the concat you had.

    1) The predicate is not being "pushed down". If you read the predicate that was at line 15 you can see that it cannot be applied until after all three tables have been joined. Your problem is that Oracle thinks the join A->D will return a very small amount of data and has decided to do that join first and then for each (relevant) row in B, attempt the join then apply the filter. Unfortunately your stats have deceived the optimizer so that result set created by joining A->D is large and A->B first would be a better bet.

    2) The only option to see the optimized query (and this doesn't always work, and it's not always "legal" SQL) is to optimize the query with the 10053 trace event (or its modern equivalent) enabled and look for the last "UNPARSED QUERY" in the trace file.

    3) SQL Baselines simply capture the text of the OUTLINE_DATA that you've manipulated, and there are cases where Oracle has discovered that the set of hints does not define a plan unambigiously. This is why several hints have become more complex over time, typically adding parameter, and some baselines (outlines) don't reproduce the plan that they came from.  The push_pred hint, for example, changed from a simple, naked "push_pred" into a push_pred() with parameters desribing which predicate from which table should be pushed.


    Jonathan Lewis

This discussion has been closed.