RE: Why isn't Oracle using my index ? <humour>

Was the table analyzed?
Maybe Oracle won't always choose the index if the query returns less than 0.00000067% (1 in 150 million) rows, but I have full faith that it will do the right thing if the query returns less than 0.0000005% of the rows.

Doesn't your laptop come with some kind of Solitaire program for distraction during airport downtime?

> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
>
> I'm sure that most of you have seen various claims
> along the lines of:
> Oracle will use an for a query that
> access less than X% of the rows.
>
> X is usually given as something in the
> region of 2% to 5%
>
> Having a spare 40 minutes in an airport this
> evening, I asked myself -
> "How low can you go ?"
>
> In my first test case, I managed to get
> Oracle 9.2 to choose (i.e. no hints involved)
> to scan 1.5 million row, rather than use a
> primary key index to fetch one row.
> i.e. 0.000067%
> and it still did a tablescan.
>
> The result of the test also suggested that
> I could safely bet on getting a tablescan
> to select one row in 150,000,000.
>
> Unfortunately I don't have enough spare
> disk space to check the theory.
>
>
> So next time someone says:
> Oracle will use an index for
> accessing less than ....
> you know what to say.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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).