Re: Why INDEX SCAN instead of FULL SCAN?

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.

Alternatives would be:

1. Merge join. Both tables are read in full, sorted and joined.
2. Hash join. Both tables are read in full, hash table is built and tables are joined.

Looks like optimizer estimated that nested loop algorithm is the best in this case. It looks for table sizes, statistics on the tables and indexes and other information to make an estimate.

>
> Thanks,
> Sara
Received on Wed Oct 30 2002 - 16:40:42 CET