Re: Index Usage ?!

The trace seems to be from when the index is not analyzed. The CBO then uses defaults for the index statistics - leaf_blocks=25 and clustering factor=800. These defaults are much lower than when the index is analyzed and the resulting cost for using the index is very low (7 compared to 1676 for a full scan). When you analyze the index, the statistics will be orders of magnitude larger - I estimate that the clustering factor will be > 300,000, and therefore the cost of using the index exceeds that of the full scan ( still 1676 ).

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 Received on Thu Jul 24 2003 - 08:47:51 CDT