For Index ( Range Scan and Fast Full Scan Setings )in query
(1) RULE uses index range scan
(2) CBO uses Index fast full scan ( called Index FFS )
Here CBO uses multi block IO reading as per db_file_multi_block_read_count). By default, the CBO costs physical IO and logical IO the same. So it ignores the
following facts.
(A) The index range scan will be all logical IO.
(B) Now Index FFS uses Multi-block IO, so causes less physical IO compare to Index range scan. Hence the cost it gives to the FFS on the small index is
artificially low (conversely, the cost of the index range scan is artificially high).
There are 2 init.ora parameter that affects this settings.
Parameter-1 optimizer_index_caching :
————————————-
percentage of blocks expected to be found in the buffer cache during an index hit. default of 0 implies that every (logical) LIO is a (physical) PIO.
Property Description
—————————–
Parameter type Integer
Default value 0
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 0 to 100
OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the
caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the
block access patterns of different users.
You can modify the optimizer’s assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0
and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested
loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or
sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in
default optimizer behavior.
A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested
loop accordingly. Use caution when using this parameter because execution plans can change in favor of index caching.
Parameter -2 optimizer_index_cost_adj
————————————-
Property Description
—————————–
Parameter type Integer
Default value 100
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 1 to 10000
Represents relative cost of PIO’s for indexed access vs full scan. Default value of 100 indicates that an indexed access is just as costly as a full access.
On many systems (most oltp casual query systems) — the default values for these parameters are totally wrong. The default of 100 for cost adj is way too
high. The default of 0 for index caching is way too low.
The default value is 100, which means that indexes are evaluated as an access path based on the normal costing model. A value of 10 means that the cost of
an index access path is one-tenth the normal cost of an index access path.
that OPTIMIZER_INDEX_CACHING should be set to 90 and OPTIMIZER_INDEX_COST_ADJ should be set to a value which usually ranges between 10 and 50 for most online
transaction processing (OLTP) systems.
Full scans will be used when a range scan cannot. fast full scans will be used to avoid a full scan of the base table itself.
Oracle chooses index full scan instead of range scan and the performance was poor compared with range scan. I think the problem was with Clustering Factor it
was almost as high as number of rows. An index full scan is a RANGE scan in the grandest sense. It is a range scan from the beginning to the end.