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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
