| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Strange SQL plan
<srivenu_at_hotmail.com> wrote in message
news:1159239252.531441.277900_at_h48g2000cwc.googlegroups.com...
>I would appreciate if someone can explain this to me.
>
> This is the SQL
>
> SELECT ROUND (TO_NUMBER (NVL (prc.pricing_attribute53, 0)))
> monthly_fee,
> ROUND (TO_NUMBER (NVL (prc.pricing_attribute58, 0))) one_time_fee
> FROM aso_quote_lines_all ql,
> aso_line_relationships qlr,
> aso_price_attributes prc,
> aso_quote_line_details qld
> WHERE
> ql.quote_header_id = 485 AND
> ql.item_type_code = 'MDL' AND
> qlr.quote_line_id = ql.quote_line_id AND
> qlr.relationship_type_code = 'CONFIG' AND
> prc.quote_line_id = qlr.related_quote_line_id AND
> qld.quote_line_id = ql.quote_line_id AND
> qld.config_header_id = 37060
> /
>
> ----------------------------------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost |
> ----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 958 | 51732 | 3750 |
> | 1 | TABLE ACCESS BY INDEX ROWID | ASO_PRICE_ATTRIBUTES |
> 1 | 10 | 3 |
> | 2 | NESTED LOOPS | |
> 958 | 51732 | 3750 |
> | 3 | NESTED LOOPS | |
> 958 | 42152 | 876 |
> |* 4 | HASH JOIN | |
> 273 | 6825 | 57 |
> | 5 | TABLE ACCESS BY INDEX ROWID| ASO_QUOTE_LINE_DETAILS |
> 274 | 3014 | 16 |
> |* 6 | INDEX RANGE SCAN | ASO_QUOTE_LINE_DETAILS_N5 |
> 274 | | 4 |
> |* 7 | TABLE ACCESS BY INDEX ROWID| ASO_QUOTE_LINES_ALL |
> 549 | 7686 | 40 |
> |* 8 | INDEX RANGE SCAN | ASO_QUOTE_LINES_ALL_N1 |
> 2116 | | 6 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | ASO_LINE_RELATIONSHIPS |
> 4 | 76 | 3 |
> |* 10 | INDEX RANGE SCAN | ASO_LINE_RELATIONSHIPS_N1 |
> 4 | | 2 |
> |* 11 | INDEX RANGE SCAN | ASO_PRICE_ATTRIBUTES_N2 |
> 1 | | 2 |
> ----------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 4 - access("QLD"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")
> 6 - access("QLD"."CONFIG_HEADER_ID"=37060)
> 7 - filter("QL"."ITEM_TYPE_CODE"='MDL')
> 8 - access("QL"."QUOTE_HEADER_ID"=485)
> 9 - filter("QLR"."RELATIONSHIP_TYPE_CODE"='CONFIG')
> 10 - access("QLR"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")
> 11 - access("PRC"."QUOTE_LINE_ID"="QLR"."RELATED_QUOTE_LINE_ID")
>
> Note: cpu costing is off
>
> Now if i write it like this
>
> SELECT ROUND (TO_NUMBER (NVL (prc.pricing_attribute53, 0)))
> monthly_fee,
> ROUND (TO_NUMBER (NVL (prc.pricing_attribute58, 0))) one_time_fee
> FROM x aqha,
> aso_quote_lines_all ql,
> aso_line_relationships qlr,
> aso_price_attributes prc,
> aso_quote_line_details qld
> WHERE
> aqha.QUOTE_HEADER_ID = ql.QUOTE_HEADER_ID and
> aqha.quote_header_id = 485 AND
> ql.item_type_code = 'MDL' AND
> qlr.quote_line_id = ql.quote_line_id AND
> qlr.relationship_type_code = 'CONFIG' AND
> prc.quote_line_id = qlr.related_quote_line_id AND
> qld.quote_line_id = ql.quote_line_id AND
> qld.config_header_id = 37060
> /
>
>
>
> X is some dummy table which has good number of distinct
> quote_header_id's (2,805 distinct quote_header_id's out of a total of
> 4,303 rows).
>
>
> -----------------------------------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost |
> -----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 1 | 58 | 54 |
> | 1 | TABLE ACCESS BY INDEX ROWID | ASO_PRICE_ATTRIBUTES |
> 1 | 10 | 3 |
> | 2 | NESTED LOOPS | |
> 1 | 58 | 54 |
> | 3 | NESTED LOOPS | |
> 1 | 48 | 51 |
> | 4 | NESTED LOOPS | |
> 1 | 29 | 48 |
> |* 5 | HASH JOIN | |
> 2 | 36 | 42 |
> |* 6 | INDEX RANGE SCAN | IX |
> 2 | 8 | 1 |
> |* 7 | TABLE ACCESS BY INDEX ROWID| ASO_QUOTE_LINES_ALL |
> 549 | 7686 | 40 |
> |* 8 | INDEX RANGE SCAN | ASO_QUOTE_LINES_ALL_N1 |
> 1098 | | 6 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | ASO_QUOTE_LINE_DETAILS |
> 1 | 11 | 3 |
> |* 10 | INDEX RANGE SCAN | ASO_QUOTE_LINE_DETAILS_N1 |
> 1 | | 2 |
> |* 11 | TABLE ACCESS BY INDEX ROWID | ASO_LINE_RELATIONSHIPS |
> 4 | 76 | 3 |
> |* 12 | INDEX RANGE SCAN | ASO_LINE_RELATIONSHIPS_N1 |
> 4 | | 2 |
> |* 13 | INDEX RANGE SCAN | ASO_PRICE_ATTRIBUTES_N2 |
> 1 | | 2 |
> -----------------------------------------------------------------------------------------------
>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.htmlReceived on Tue Sep 26 2006 - 15:17:43 CDT
 |
 |