Hints based on the WHERE clause

The database drivers generate an index hint based on the WHERE clause, If these conditions are met:

• The query contains one single table, and therefore no joins.
• An index is defined that matches the WHERE clause.

To determine the best matching index, use these rules:

• Ignore those parts of the WHERE clause that cannot be used. Complex operations, such as pattern matching, are required, or result in multiple ranges.

  These operands are ignored:

  • LIKE expression [NOT] LIKE expression.
  • Exists [NOT] EXISTS expression.
  • OR – lists expression OR expression.
  • Set expression [NOT] IN expression.
  • Negations expression != expression

    expression <> expression

    expression NOT BETWEEN expression

    expression NOT INRANGE expression

• If you use combined columns in a query, the combined columns are replaced by combined operands that contain the base columns of which the combined column exists.

  Base columns that do not have a counterpart are ignored.

  For example:

  AND_index3 >= { :aaa , :bbb }

  is handled as:

  AND { :col1, :col2, :col3, :col4 } >= { :aaa, :bbb }

  Note: The specification of the combined column name _index<n> does not imply the use of index <n> in Oracle. For the hint generation, the column _index<n> is a combined column that contains the base columns of that index.
• From the remaining parts of the query, the columns are determined that you can use for an index hint.
• Determine the matching indexes:

  For all indexes, the fields in the indexes are compared with the usable columns in the WHERE clause.

  Indexes whose initial field is not used are not matching.

• To determine the best matching index, calculate the total weight per index. The index with the highest total weight is selected.

  If multiple indexes have the same total weight, the index with the lowest index number is selected.

• The calculation starts with the first index field, and stops when an index field is not used in the WHERE clause. All remaining fields are not considered.

  For each column, the weight is determined. By default, the weight of the column is equal to the number of occurrences of the column in the pruned where clause.

  To tune the weight determination, use the resources hint_idx_weight_equal and hint_idx_weight_range, as shown in this example:

  The result of the expression: <column weight> * (<hint_idx_weight_factor>)^{(position -1)} is added to the total weight.