I would like to use the Index Scan even though it is costlier. Could you suggest me an init.ora SPfile parameter to force the Cost Based Optimizer (CBO) to make a statement execution path to us an Index?
Hi Ms. Miller,
I suggest you try changing parameter for your OPTIMIZER_INDEX_COST_ADJ = int. This .ora SPFile can regulate the cost of Index versus FTS. The sole purpose of this parameter is merely to "adjust” the matching costs linked with an Index to more precise relative I/O costs involving index and FTS. This parameter has exceptionally straightforward impact on how the CBO costs used index based on access path. The value of optimizer_index_cost_adj as a percentage, regulate the cost of an index related to vary access scan corresponds in percentage of total index cost. A default value of 100 thus has no effect on the general cost in index related to access path. Depending on the parameter input, CBO will accurately reflect differences in cost associated in single block I/O’s versus multi-block I/O’s. It will automatically incorporate discrepancies in its costing analysis.
You may also consider this parameters that affect CBO performance:
OPTIMIZER_MAX_PERMUTATIONS = int Max join permutations per query block. It affects a specified maximum limit of permutations considering queries with join then choose an execution plan. This should be set to a lower value to manipulate parse time query.
OPTIMIZER_SEARCH_LIMIT. Shows detailed and specific maximum table query allowed by the Optimizer. This prevents the excessive amount of time evaluating possibilities table combination.
Adjusting Parameters will depend on the environment requirement. If it’s Data Warehousing, OLTP (online transaction processing) or Batch Processing, please do take note that optimizer consider these parameters to assess every execution plan it analysis produce in CBO.
Regards,
A.Wright
