Use of Oracle Index hint

The distribution of data stored in the database is clearer than the Oracle optimizer, where the optimizer cannot make the most of the query path selection, using the hint (hint) artificial fixed query path to a certain extent to generate a better execution plan.
In SQL queries, indexes are one of the most important and common means of quickly querying data, and this article will discuss the use of hint related to indexes.
How to use 1.HINT:
Select/*+ INDEX (tab pk_tab) */* from Test.tab;
Above is a simple example using hint, where tab is the name of the table, Pk_tab is the name of the index, and if the table has an alias, then the alias should be used. If the name of the table is preceded by a schema name, you do not need to specify the schema names during the use of index, just specify the table name.
2.HINT tips:
The use of indexes in an Oracle database includes: Index range scan, index hop Scan, index fast full scan. The following is a discussion of the Oracle database tips for indexing, as shown in the following table, which lists the tips for different scan methods:

Index	Index range Scan	Index Hop Scan	Index Fast Full Scan
Index	Index_rs	Index_ss	Index_ffs
Index_asc	No_index_rs	No_index_ss	No_index_ffs
Index_desc	Index_rs_asc	Index_ss_asc	Index_asc
	Index_rs_desc	Index_ss_desc	Index_desc

The following examples illustrate the use of different index hint:
1). Use the index.
Select/*+ INDEX (tab pk_tab) */From Tab;
For such a SQL, the query uses index Pk_tab, and as to how to scan the index is Oracle's own decision. If you can determine which scan method is the fastest, you can explicitly scan the index:
Select/*+ index_rs (tab pk_tab) */From Tab;
Select/*+ index_ss (tab pk_tab) */From Tab;
Select/*+ index_ffs (tab pk_tab) */From Tab;
2). Do not use indexes.
Select/*+ no_index_rs (tab pk_tab) */From Tab;
For such a SQL, the query does not use an index range scan, then full table scan of the table is used, and NO_INDEX_SS and no_index_ffs can be used, depending on the situation, without the use of an index hop scan and an index fast full scan, which will use the table's full table scan.
3). The index is being sorted using.
SELECT * from the tab order by ID;
For such a SQL, the ordering uses the positive order, then you can use *_ASC related hint, for example, select/*+ index_asc (tab pk_tab) */* from tab order by id;index_ ASC indicates that the index is scanned in a forward order. Depending on the situation, you can also use *_ASC of other scan types.
4). reverse sort using index.
SELECT * from the tab order by id desc;
For such a SQL, sort using the reverse order, then you can use the *_DESC related hint, for example, select/*+ Index_desc (tab pk_tab) */From the tab order by ID desc;index_ DESC indicates that the index is scanned in reverse order. Depending on the situation, you can also use *_desc of other scan types.

If the index contains all the columns found, or if the indexed columns satisfy the query, you can use the index to quickly scan the related hint.

Reprint: http://blog.itpub.net/23135684/viewspace-710456/

Use of Oracle Index hint