parameter optimizer_index_cost_adj=10

[ssmith]

I have posted a thread previously regarding queries not using the bitmap index, and I have been informed by Oracle to use the parameter optimizer_index_cost_adj=10. This will ensure the optimizer will use your indexes. Has anyone had any experience with this parameter? Thanks.

[julian]

With a value of 10 you make the IAP = (Index Access Path) look 10 time less expensive (10 = 100/10).

I personally think that the default value of 100 should not be touched. Sometimes a FTS might be better than an IS.

[justadba]

Actually at last years IOUG-Live conference, they were pushing the usage of setting the optimizer_index_cost_adj to 10 or so to make index usage more attractive to the optimizer.

Another good tip os to set the parameter optimizer_index_caching to a higher percentage. This tells the optimizer approximately how much percentage of the index it can expect to find in the buffer cache rather than have to go to disk. A setting of 90 can make a difference in how the optimizer works your queries.

And yes, sometimes a full table scan is the better way to go, but not always. It's a matter of knowing both your data and your application.

[tekion]

justadba,
In regards to optimizer_index_caching, what buffer in the SGA is it using for cache?

On another note, could you give a conceptual example where a full table scan is better? Is a full table scan better when you have a large table? Thanks.