Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle using my index ? <humour>

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

From: Jacques Kilchoer <>
Date: Thu, 24 Apr 2003 17:16:48 -0800
Message-ID: <>

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 []
> 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:
Author: Jacques Kilchoer

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Thu Apr 24 2003 - 20:16:48 CDT

Original text of this message