Oracle-L: Re: Why isn't Oracle using my index ? <humour>

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 26 Apr 2003 09:31:39 -0800
Message-ID: <F001.0058A34E.20030426093139@fatcity.com>

Given that I'm trapped by a blizzard, I gave it a shot and it was surprisingly simple. I'm up to 2 million right now and the optimizer is choosing a full table scan over an index access:

pollux.stats.scott> explain plan set statement_id = 'jl-002' for

2 select n from t3 where n=:b1
3 /

                                                          Plan for SQL jl-002

     card

operation
opt

1 SELECT
STATEMENT
CHOOSE
1 TABLE ACCESS FULL
T3
ANALYZED

just to prove that there is a suitable index, and Oracle wouldn't even have to access the table. Everything it needs is in the index.

pollux.stats.scott> explain plan set statement_id = 'jl-003' for

2 select /*+ index(t3 t3_pk) */ n from t3 where n=:b1 3 /

                                                          Plan for SQL jl-003

     card

operation
opt

1 SELECT
STATEMENT
CHOOSE
1 INDEX UNIQUE SCAN
T3_PK
ANALYZED Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Wolfgang Breitling
INET: breitliw_at_centrexcc.com

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: ListGuru_at_fatcity.com (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). Received on Sat Apr 26 2003 - 12:31:39 CDT