Why would you like to use the TermObs.OBSID's index ?
You must access the TermObs for 2 things
1. read the ObsID for the where clause
2. read termID for the select clause
You have to read the whole tables since you do not have a very restricting
where clause.
That's what Oracle is thinking since it's using a hash join.
By doing a full table scan, Oracle reads ObsID and termID in one shot.
By using an index it would have to access the index (for ObsID) and the
table (for termID), since you're bringing back a lot of rows then it's
cheaper to do a full table scan.
Stephane
-----Original Message-----
Sent: Tuesday, March 25, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Hi:
We have an oracle 8173 running on Solaris 2.8. I am trying to understand why
oracle chooses the Full Table scan in it's explain plan in the floowing
query:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN;
SQL> select TermID from Observationlist, TermObs where ID = ObsID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9477 Card=2633337 By
tes=44766729)
1 0 HASH JOIN (Cost=9477 Card=2633337 Bytes=44766729)
2 1 INDEX (FAST FULL SCAN) OF 'PK_OBSERVATION' (UNIQUE) (Cos
t=834 Card=2513357 Bytes=15080142)
3 1 TABLE ACCESS (FULL) OF 'TERMOBS' (Cost=1126 Card=2633337
Bytes=28966707)
SQL> SET AUTOTRACE off;
SQL> desc Observationlist;
Name Null? Type
----------------------------------------- -------- ------------------------
----
ID NOT NULL NUMBER
GENEID NUMBER
CURATIONTYPE NUMBER
PROTEOMEREFID NUMBER
SOURCEID NUMBER
SOURCETABLE VARCHAR2(25)
DESTID NUMBER
DESTTABLE VARCHAR2(25)
DESTDATE DATE
REFERENCETYPE VARCHAR2(1)
EVIDENCECODE NUMBER
CURATORID NUMBER
EDITORID NUMBER
UPDATESTAMP DATE
CURATIONSTATUS VARCHAR2(1)
ORIGINALSTAMP DATE
NEXTOBS NUMBER
TARGET VARCHAR2(15)
REFTARGET VARCHAR2(15)
TOOL VARCHAR2(25)
OLDGENEID NUMBER
SQL> desc TermObs;
Name Null? Type
----------------------------------------- -------- ------------------------
----
OBSID NUMBER
TERMID NUMBER
CONTEXT NUMBER
SQL> select count(*) from Observationlist;
COUNT(*)
----------
2513357
SQL> select count(*) from TermObs;
COUNT(*)
----------
2633337
TermObs.OBSID has FK constaint pointing to Observationlist.ID.
TermObs.OBSID is also indexed. Observationlist.ID is PK in the table. Both
tables have been analyzed (using compute statistics). I would expect the
query to use index on TermObs.OBSID. I tried to put hints in but they
resulted in much higher "cost" than the one without.
Any explaination why TermObs.OBSID's index is not used?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Paquette
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
