> My only doubt is: > > 1.After analyzing the index,CBO doesn't read the index because > it is costlier in terms of I/O and other resources.
The point is, that index access is cheap in reality, but CBO thinks it's very expensive and chooses next best executin plan in it's opinion, but this opinion is actually wrong, because unset optimizer_ parameters for example. One more thing, your test query returned no rows, it could be that with index access Oracle didn't even have to visit any table blocks because no relevant keys found in index. But when you'll have keys matching your query conditions in future, index access might get slower as well (here's where clustering_factor comes into play). > > 2.But without analyzing,CBO uses the index now,which is supposed > to be coslty,and hence incurs the same I/O and other resources . Without analyzing, the index cost in reality is still low, and with super-optimistic default statistics CBO luckily picks that path. > > 3.when the role of CBO is over,the h/w resources comes into play > to read the index.even now it reads the full index and the > response is faster.how is it so ? No, according plan in your orignal post, index range scan was used. Btw, I submitted a simple research about CBO table/index access costs to comp.databases.oracle.server yesterday under subject "2 Oracel doubts", you might want to read that one as well to get more understanding on your issue. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
