Re: Why INDEX SCAN instead of FULL SCAN?

<snip>

> > > Execution Plan
> > > ----------------------------------------------------------
> > > 0 SELECT STATEMENT Optimizer=CHOOSE
> > > 1 0 NESTED LOOPS
> > > 2 1 TABLE ACCESS (FULL) OF 'E'
> > > 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'D'
> > > 4 3 INDEX (UNIQUE SCAN) OF 'SYS_C002462' (UNIQUE)
> > >
> > > What confuses me is why an INDEX (UNIQUE SCAN) is done.
> > > Why not a full SCAN (ie, TABLE ACCESS(FULL)), esp if
> > > a full scan is done on E. One would think that both
> > > tables would be accessed similarly. Also, it
> > > seems like the extra operation (INDEX) is more
> > > time consuming than just accessing D via FULL SCAN.
> > >
> > > In fact, I can't even see how the INDEX operator works
> > > here because there is no value to look for (since no
> > > literal was specified in the query). So, what does
> > > the INDEX actually index?
> > >
> > > Can anyone clarify this?
> >
> > I think you need to read "Designing and Tuning for Performance" documentation to
> > make it completely clear.
> >
> > In this particular case:
> > Table E is read in full table scan mode.
> > For each row in E unique index entry is found (or not found) in primary key
> > index.
> > Table D row is found using ROWID from index.
> >
>
> I know you're right about this, but that is ___NOT____
> what the plan says. The plan says that INDEX is a child
> statement (ie, is a parent to no other statement) and that
> the table scan of E does not drive the index.
>
> You can even see this in Figure 4-4 of the Oracle documentation
> located at: (look halfway down the page)
>
> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/optimops.htm
>
> You'll see that the query tree __IN NO WAY__ indicates that E is
> used to drive the index lookup on D.
>
> In fact, the second bullet point in that documentation reads:
>
> For each row returned by step 2, step 4 uses the
> emp.deptno value to perform a unique scan on the pk_dept
> index.
>
> and I find this perplexing, given that the figure (and the
> explained plan) does not indicate this relationship between
> statements 4 and 2.
>
> I'd love to hear an explanation of this apparent contradiction.
>
> Sara

OK, I see. I have problems reading plans even after I did it thousands of times. The usual way would be to start with the lowest level. Doesn't work in your case, you're right. But my experience says in this case, that you need to look one or two levels up. Usually to find actual join operation. In this case, it's nested loops. In the nested loops we always have 2 tables. First would be driving table, E in this case. Then we see index search on the second table. We can deduce that it only can be used if optimizer uses value from E for this index search.

I can't say that it's exactly clear. And in some cases it's even more strange looking. But in most cases you can understand the plan.

In short, explained plan is not perfect and you need some experience and somehow warped mind to read it. Received on Thu Oct 31 2002 - 17:05:21 CET