Which spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
To make CBO behave like RBO on steroids is achieved by manipulating two parameters, OPTIMIZER_INDEX_CACHING and OPTIMIZER_ INDEX_COST_ADJ. Those two parameters regulate the following things:
● OPTIMIZER_INDEX_CACHING makes CBO assume that for any index certain percentage of blocks is cached. If the value of the parameter is 80, CBO assumes that 80% of the index blocks are cached. According to Jonathan's book and private communication, this parameter has no effect on single table access paths, it only affects in-list iterators and nested loop joins.
● OPTIMIZER_ INDEX_COST_ADJ parameter defines the cost of a single-block I/O as a percentage of a cost of a multi-block I/O request. Single block I/O is most frequently used when retrieving data from an index, while multi-block I/O is normally used when retrieving data from a table. Reference manual claims that this parameter determines a cost of an I/O against index as a percentage of a cost of an I/O against tables.
These two parameters can be manipulated both on the instance level, with ALTER SYSTEM or at the session level, with ALTER SESSION.
Those two parameters will make CBO behave like RBO if an index is there, it will be used. Unfortunately, this approach suffers from the same shortcoming as RBO if an index is there and we don't want it used, we have to explicitly disable it, by using hints or otherwise. That is something that we can live with, in the OLTP world. Of course, this is the real world so databases are almost never pure OLTP databases.
