Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 25 Apr 2003 09:36:56 -0800
Message-ID: <F001.0058981E.20030425093656@fatcity.com>


Pat,

The "use an index for any query returning fewer than x% of the rows" thing is a myth carried over since version 5, when it was *also* untrue. Check "When to Use an Index" at www.hotsos.com/catalog for the story.

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-----
Wilton
Sent: Friday, April 25, 2003 11:42 AM
To: Multiple recipients of list ORACLE-L

Jonathan,

I don't believe it. CBO sux eggs, but please send a 10053 trace.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Fri, 25 Apr 2003, Boivin, Patrice J wrote:


> 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).
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: jwilton_at_speakeasy.net 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).
Received on Fri Apr 25 2003 - 12:36:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US