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

Is this a change in 9i, or what it just a myth carried over from version to version?

The optimizer is supposed to be able to assess when to use an index vs. when not to use one.

Were your tables analyzed?

Pat.

-----Original Message-----
Sent: Thursday, April 24, 2003 6:22 PM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

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