Purpose
The purpose of optimizer_index_cost_adj parameter is to force use index even the cost is high. (decides large-table full-table scans or index range scans)
Usage
Current setting:
SQL> show parameter optimizer_index_cost_adj;
-- default is 100
-- ranges between 1 and 10000
(options like -> 10 | 100 | 1000)
Change setting permanently:
add this into init.ora file;
*.optimizer_index_cost_adj=10
-- "10" is to force it uses index
-- "100" is the default value
-- "1000" is not to force and it tries to different method (large-table full-table) to search data
Then restart the instance to perform.
Change setting for current instance - temporary:
SQL> alter system set optimizer_index_cost_adj = 10;
Result
SQL> show parameter optimizer_index_cost_adj;
-- output; 10
