Re: Why INDEX SCAN instead of FULL SCAN?

afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0210300740.2009446d_at_posting.google.com>...
> saraypatchnick_at_hotmail.com (Sara) wrote in message news:<6c30a166.0210292251.5e0ddbd9_at_posting.google.com>...
> > Hi,
> >
> > I'm trying to understand why, when there are no
> > indexes and no other constraints except primary
> > keys, why Oracle does an INDEX UNIQUE SCAN on
> > one of table involved in a simple equijoin.
> >
> > For example, if I have the following schema:
> >
> > CREATE TABLE d (
> > d_did NUMBER PRIMARY KEY,
> > dname VARCHAR2 (20));
> >
> > CREATE TABLE e (
> > e_eid NUMBER PRIMARY KEY,
> > e_did NUMBER,
> > e_name VARCHAR2(10),
> > CONSTRAINT e_fk1 FOREIGN KEY (e_did) REFERENCES d (d_did));
> >
> > and I do the following query:
> >
> > SELECT *
> > FROM d, e
> > WHERE e_did = d_did;
> >
> > I get the plan:
> >
> > 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 Received on Thu Oct 31 2002 - 03:22:21 CET