Re: strange cost in explain plan
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Oct 2007 08:39:59 +0100
Again, you are quoting a statement that isn't strictly true.
"The access order is from top to bottom if the indent is the same"
First, it's only an approximation in simple execution plans
anyway, and should be restated more like: "the order of
operation of the child lines of a single parent is from top
to bottom (assuming you've printed the plan correctly)".
But then the whole "scalar subquery in the select list" is a
special case. I agree that visually it would make a little more
sense to show the driving query first and then list the subqueries,
but it's just not done that way.
I thought your example would be of sufficient general interest
that I've written a little blog item about it. It doesn't say anything
I haven't put in these email messages, though.
http://jonathanlewis.wordpress.com/2007/10/12/scalar-subqueries/
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: Sat, 13 Oct 2007 06:39:55 +0800
From: "qihua wu" <staywithpin@xxxxxxxxx>
Subject: 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@xxxxxxxxxxxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
