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