strange cost in explain plan

From: qihua wu <staywithpin_at_gmail.com>
Date: Fri, 12 Oct 2007 10:10:51 +0800
Message-ID: <2689c1070710111910o25a3f53dt60abd373197122dd@mail.gmail.com>

Hi, everyone,

For the plan, the cost of the parent should be equal or larger than the child. But in the following plan, the total cost (Id=0) is 2, but it's child "HASH UNIQUE "(ID=1) has a cost of 2083 which is much larger than 2, how could it be like that?

Plan hash value: 3028553145

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1 | 482 | 2 (0)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 48 | 2083 (1)| 00:00:25 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | ATTRIBUTE_VALUE | 1 | 24 | 3 (0)|
00:00:01 |
| 4 | NESTED LOOPS | | 11 | 528 | 2082 (1)| 00:00:25 |
|* 5 | TABLE ACCESS BY INDEX ROWID| ATTRIBUTE_VALUE | 11 | 264 | 2049 (1)|
00:00:25 |
|* 6 | INDEX RANGE SCAN | IDX_ATTRIBUTE_VALUE_2 | 19155 | | 59 (0)| 00:00:01
|
|* 7 | INDEX RANGE SCAN | IDX_ATTRIBUTE_VALUE_1 | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | PATH_RELATIONSHIP_TEMP | 1 | 482 | 2 (0)| 00:00:01
|

---

Predicate Information (identified by operation id):

---

2 - filter(ROWNUM=1)
5 - filter("A"."ATTR_STR"=:B1)

Select (select distinct b.attr_str
from ATTRIBUTE_VALUE a,
ATTRIBUTE_VALUE b
where a.locale_lang_id = :a

and a.attr_id = :b
and a.attr_str = x.product_line_name
and a.node_id = b.node_id
and b.locale_lang_id = :c
and b.attr_id = :d
and a.change_lifecycle_id = 0
and b.change_lifecycle_id = 0

and rownum = 1)
from PATH_RELATIONSHIP_TEMP x;

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 11 2007 - 21:10:51 CDT