Oracle Hash Partitioned IOT & Sub-optimal SQL Oracle Database Tips by Donald Burleson

Question: I have some large, hash-partitioned IOT's, and the optimizer sometimes chooses to do fast-full scans rather than a range scan with a nested-loop join. 

I want to turn off (or at least make it look expensive to the optimizer) both fast full scans and skip scans.  Are there still some hidden init params for these features (i.e. _fast_full_scan_enabled)? 

Or is there a way to influence the use of these access methods via the optimizer_index_cost_adj parameter?

Answer:  First, more information is required:

• Does your SQL have function predicates on partition keys?

• Are your fast-full scans for the whole table, of just a partition?

I would try to adjust the CBO statistics first, before adjusting parameters.  David Aldridge has some great work on improving CBO stats for partitioned tables with sub-optimal SQL

• Problem with functions in predicates in partitioned tables - When you place a predicate on a function of a partition key column then the optimizer is prevented from pruning unless there is a check constraint to indicate logical equivalence between the column and the function of the column.
   
• Problem with partition pruning and partition key statistics - The immediate sign of a problem was the extensive use of nested loop joins, and that led to the observation that the cardinality of the result set from one of the partitioned tables was low.

>> I have some large, hash-partitioned IOT's

Dr. Hall notes a new 10g feature for global indexes on hash partitioned IOT's:

"Support for hash partitioned global indexes has been added in Oracle 10g which can improve performance when a small number of leaf blocks are experiencing high levels of contention."