An index hint instructs the optimizer to consider the use of an indexed table scan for a specified table. You can optionally specify one or more indexes in the hint. If no indexes names are specified in the hint, Oracle optimizer evaluates the cost of all indexes for a table and uses the most efficient.
Instructing the optmizer to use an index is just a hint, it’s Oracle optimizer decision to make use of an index or not. If the Oracle optimizer recognizes that the cost of full table scan is cheaper than using an index, your index hint will be ignored. And there are more factors that optimizer may favor the use of full table scan rather than using an index.one among them is db_file_multiblock_read_count initiliazation parameter.
db_file_multiblock_read_count is a parameter which specifies the number of blocks than can be read by Oracle simultaneously.
If you increase the value in the db_file_multiblock_read_count rather than the default value, then Oracle favours more of full table scan’s rather than index usage. If you decrease the value in the db_file_multiblock_read_count , then Oracle favours more of index usage rather than full table scans.
Another parameter that can play an important role in choosing an index is optimizer_index_cost_adj.
Example of using an index hint
SELECT /*+ INDEX(EMP EMP_IDX1) */ emp_name
FROM EMP WHERE EMP_ID=34344
SELECT /*+ INDEX(EMP) */….
IN the index hint first parameter specifies the table name or alias for the table . And the next list specifies the list of indexes for consideration.
INDEX_ASC(table [index]) :
Instructs the Optimizer to scan an index in Ascending order. But i dont see any difference between the index hint and index_asc hint, Since Oracle always scans an index in ascending Order.Index and index_asc are just like synonyms with out any difference in performance in any case.
An example of INDEX_ASC hint
CREATE INDEX idx_customers_id
ON customers(cust_id);
set autotrace traceonly explain
SELECT *
FROM customers
WHERE cust_id = 99999;
SELECT /*+ INDEX_ASC(customers idx_customers_id) */ *
FROM customers
WHERE cust_id=99999;
INDEX_DESC(table [index]) : Instructs the Optimizer to scan an index in descending Order. This hint is most useful in distributed environments. Say if you want to get the data from a table that is inserted to a table recently based on some date, this hint can be of immense help for you.
An example of INDEX_DESC hint
set autotrace traceonly explain
SELECT *
FROM customers
WHERE cust_id = 99999;
SELECT /*+ INDEX_desc(customers idx_customers_id) */ *
FROM customers
WHERE cust_id=99999;
INDEX_FFS(table [index]) : Instructs the optimizer to do a full scan of an index rather than a full scan of a table. The index scan can sometimes run faster, but if and only if every column in the WHERE clause for the specified table exists in the index.
Does not read every block in the index structure, contrary to what its name suggests An index full scan processes all of the leaf blocks of an index, but only enough of the branch blocks to find the first leaf block
Provides ordered output, can be used to avoid sorts for ORDER BY clauses that specify index column order.
An example of Index_FFS hint
set autotrace traceonly explain
SELECT first_name
FROM employees e;
SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name
FROM employees e;
Index_combine( table [index1 index2 ....]) : If you have ever worked in an datawarehouse environment, i am sure you must have used this hint. Index_combine instructs the Optimizer to use one or more bitmapped indexes combined in an boolean relatinship to get the indexed row of a table.
Mainly this hint is used while accessing the data from a fact table in a datawarehouse environment. If one or more bitmap indexes are created on the dimension keys of a fact table , Oracle combines all the indexes with a boolean “AND” , “OR” of bits in the bitmap index to give you result.
An example of Index_combine hint
SELECT /*+ INDEX_COMBINE(e geo_dmnsn_idx proft_dmnsn_idx) */ *
FROM fact_trans_amt
WHERE (geo_id = 108 OR proft_ctr_id = 110);