Re: Strange SQL plan

Jonathan Lewis wrote:
> Charles Hooper has supplied the most significant
> portion of the answer - which addresses why the
> final cardinality has dropped so much. Transitive
> closure has kicked in, created a new predicate,
> and introduce an extra factor of 1/2000 (roughly)
> to the arithmetic.
>
> The specific answer to the question
> > Why is there a drop in cardinality from ASO_QUOTE_LINE_DETAILS.
>
> is that it is in a different place in the join order.
> When it drives the join Oracle says: "how many rows
> do I get from this table when it is the first table".
>
> When it appears as the second table in the nested loop join
>
> | 4 | NESTED LOOPS | |
> 1 | 29 | 48 |
> |* 5 | HASH JOIN | |
> 2 | 36 | 42 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | ASO_QUOTE_LINE_DETAILS |
> 1 | 11 | 3 |
>
> Oracle says - for each row I produce from the hash join
> I will visit the ASO_QUOTE_LINE_DETAILS. How
> many rows will I get each time, given the values I now
> have for the joining columns at this point in time. And
> by this time, the error introduced by the extra predicate
> has already done its damage to the hash join.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Impressive answer. Thanks for the terminology correction regarding "predicate pushing" and "transitive closure" - I was trying hard to forget the term "transitive closure" and the damage that it can cause when an application is moved from one Oracle release to another (so painfully clear in your book).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Sep 26 2006 - 16:43:13 CDT