Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Strange SQL plan

Strange SQL plan

From: <srivenu_at_hotmail.com>
Date: 25 Sep 2006 19:54:12 -0700
Message-ID: <1159239252.531441.277900@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 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("AQHA"."QUOTE_HEADER_ID"="QL"."QUOTE_HEADER_ID") 6 - access("AQHA"."QUOTE_HEADER_ID"=485) 7 - filter("QL"."ITEM_TYPE_CODE"='MDL') 8 - access("QL"."QUOTE_HEADER_ID"=485) 9 - filter("QLD"."CONFIG_HEADER_ID"=37060) 10 - access("QLD"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID") 11 - filter("QLR"."RELATIONSHIP_TYPE_CODE"='CONFIG')
  12 - access("QLR"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")   13 - access("PRC"."QUOTE_LINE_ID"="QLR"."RELATED_QUOTE_LINE_ID")

Why does the plan change so dramatically ? Why is there a drop in cardinality from ASO_QUOTE_LINE_DETAILS.

thanks & regards
srivenu Received on Mon Sep 25 2006 - 21:54:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US