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: RE: Index behavior

Re: RE: Index behavior

From: Saminathan <saminathans_at_myrealbox.com>
Date: Wed, 05 Nov 2003 15:29:34 -0800
Message-ID: <F001.005D5B28.20031105152934@fatcity.com>


Hi Goulet,

The clustering factor on the index=37930 number of distinct keys=38357
number of leaf blocks=1075

Thanks
Sami

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 05 Nov 2003 13:49:28 -0800

Can I ask for the following:

  1. The clustering factor on the index
  2. number of distinct keys
  3. number of leaf blocks

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Wednesday, November 05, 2003 4:05 PM To: Multiple recipients of list ORACLE-L

But that doesn't really explain why the optimizer chooses an FTS with a predicate that presumable is more selective (name like 'ABC%') and an index scan with a predicate that presumable is less selective (name like 'AB%'). I could understand it if it were the other way around. Is there a histogram on the name column?

At 11:34 AM 11/5/2003, you wrote:
>Sami,
>
> Your problem is not with the index, but rather the cost based
> optimizer. Most of us have been beat severely over the head and
> shoulders through the years that full table scans are a BAD thing, me
> included BTW. Well, it's time for the old dog to learn new tricks. So
> that I'm not a long winded person, take a look in Select magazine, 3rd
> qtr 2003, for the article "In Defense of Full Table Scans" by Jeff
> Maresh. For a long time the CBO was a mystery to me as well especially
> when it did unexpected things like this. I've applied Jeff's ideas on
> computing an index's efficiency to see if it explained what the CBO
> did. Amazingly in 95% of the cases I've analyzed it made absolute sense.
>
> I'm including Jeff with a courtesy copy of this message so that
> 1) I can pat him for making the waters clear and 2) so he can add
> anything he desires.
>
>Dick Goulet
>Senior Oracle DBA
>Oracle Certified 8i DBA

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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: Goulet, Dick
  INET: DGoulet_at_vicr.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: Saminathan
  INET: saminathans_at_myrealbox.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 Wed Nov 05 2003 - 17:29:34 CST

Original text of this message

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