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?
>
> Thanks,
> Sara

Hi,

I dont know what you mean to say by this statement:

"when there are no indexes and no other constraints except primary keys, why Oracle does an INDEX UNIQUE SCAN "

When you create a PRIMARY KEY, It automatically creates a UNIQUE index. The index on table D is "SYS_C002462". You can check this by issing the command

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='D'. Now coming to why it is using index is because you have it. Therefore it does not do a FULL SCAN.

Regards,
Sagi Received on Wed Oct 30 2002 - 17:54:36 CET