Why INDEX SCAN instead of FULL SCAN?

From: Sara <saraypatchnick_at_hotmail.com>
Date: 29 Oct 2002 22:51:00 -0800
Message-ID: <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 Received on Wed Oct 30 2002 - 07:51:00 CET

Original text of this message