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.
memb_ora_2400.trc
Description: Binary data
