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

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.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person   who can answer the questions, but the   person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).