Using Oracle Index Hints in SQL statements

Using Oracle Index Hints in SQL statements
Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for a certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements.
In this article, we shall see how to specify INDEX hints and what the advantages of the same are.
1      How to specify Hints
The Hints are enclosed in comment, /* comment */, in an SQL statement, and can only be specified with SELECT, DELETE and UPDATE keyword.
SELECT /* comment */ ........ ;
All hints should start with a + sign. This tells the SQL Parser that the SQL has a hint specified with it.
SELECT /*+{hint} */ ........ ;
2      Using INDEX Hints
When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specified in the hint. In this case, the optimizer does not go for a Full Table Scan nor does it use any other index. In addition, it does not calculate the cost of the Index to be used.
If no INDEX hint is specified the optimizer determines the cost of the each index that could be used to access the table and uses the one with the lower cost.
If there are multiple indexes specified with the Hint then the optimizer has to determine the cost of each index to be used with the specified table. Once that is determined, it uses the Index with the lower cost to access the table. In this case, the optimizer does not do a FULL Table Scan. Also note that, the optimizer may choose to use multiple indexes and then merge the result sets to access the table. This method is used if the cost is low.
Syntax:
/*+ INDEX ( table [index [index]...] ) */
Where:
table specifies the name or alias of the table associated with the index to be scanned.
index specifies an index on which an index scan is to be