There are two things you can do
Leave the index un-analyzed if it works for you ( I have a few tables where I use that "trick")
Set optimizer_index_cost_adj to a value lower than 100 - again if it works for you. Test that it does not adversely affect other queries. Many advocate that it should be set lower but I have not had any luck with it.
At 04:24 AM 7/24/2003 -0800, you wrote:
Hi Tanel,
<quote> did you analyze your table in addition to index as well? first time you were probably using RBO, which always counts index access better than table access. </quote>
i have analyzed PROFILE table also and hope it's CBO by default in 9i. anyway,it is CBO right from the beginning in my case here.
SQL>select num_rows,avg_row_len,chain_cnt from user_Tables where table_name='PROFILE';
NUM_ROWS AVG_ROW_LEN CHAIN_CNT BLOCKS
-------- ----------- --------- ------
736820 168 42 17407
<quote>because of optimizer_index_cost_adj and optimizer_index_caching parameters.</quote>
optimizer_index_cost_adj = 100 optimizer_index_caching = 0 db_file_multiblock_read_count = 16
<quote>it's called index skip scanning</quote> Thanx for the info Tanel. I was not knowing this.
As u said ,I have attached the Trace file also.
Kindly throw some light on this Tanel.
Regards, Jp.
Content-Disposition: attachment; filename==?iso-2022-jp?Q?memb=5Fora=5F2400.trc?= Content-Type: application/octet-stream
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).
