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

Whew.... my presentation is 98 pages, and 1.5 hours long.... glad it isn't me your talkin' about!

RF

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 4/24/2003 5:56 PM

...For those of you keeping score, I believe that someone will offer the disputed claim as fact on page 37 of his slide show that will commence at
11:45am EDT in the Southern Hemisphere 2 Ballroom at the Walt Disney World
Dolphin Hotel.

However, since (a) the presentation's scheduled duration is only 30 minutes,
(b) the slide show contains 79 pages, and (c) the content of page 39 has nothing whatsoever to do with the session abstract, I doubt that the author
will get around to talking about it.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101 in London, Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule details... - IOUG-A Live 2003, Orlando, 10am Monday 28 April: "Oracle Operational Timing Data"

-----Original Message-----
Sent: Thursday, April 24, 2003 4: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: Cary Millsap
  INET: cary.millsap_at_hotsos.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: FREEMANR_at_tusc.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).