| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle using my index ? <humour>
BTW,
I have noticed that size of db_block_buffers, and db_multiblock in
particular make a difference in that piece of conventional wisdom.
Oracle OCP DBA
-----Original Message-----
Sent: Friday, April 25, 2003 12:42 PM
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:Received on Fri Apr 25 2003 - 13:53:36 CDT
> 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: INET: Paula_Stankus_at_doh.state.fl.us 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).
 |
 |