Re: strange cost in explain plan

Hi, Jonathan Lewis

I am very glad that you replies my question, I am reading your book about CBO fundamental, an excellent book. I haveanother question about the plan, please help me out.

The access order is from top to bottom if the indent is the same. But for this case, I think the actuall access order is first "full table scan the main table", and then process the subquery based on the data return from "full table scan". But from the output of the explain plan, it first process the subquery, and then full table scan, I don't know why.

Thanks,
Qihua

On 10/12/07, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> The statement: "the cost of the parent should be equal or larger than the
> child" is not strictly true.
>
> Your basic plan is for
> select {} from PATH_RELATIONSHIP_TEMP x;
>
> You then have a scalar subquery in the select list.
> Oracle therefore shows you two independent plans
> in the plan table. One for your driving query - which
> as a cost of two, and one for (each) scalar subquery,
> which has a cost of 2083 EACH TIME IT EXECUTES.
>
> But Oracle does not know how many times the scalar subquery
> will run, and does not make any allowance for multiple executions
> in the total cost of the query.
>
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> > Date: Fri, 12 Oct 2007 10:10:51 +0800
> > From: "qihua wu" <staywithpin_at_gmail.com>
> > Subject: strange cost in explain plan
> >
> > 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
>
>
>

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