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 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.
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} */ …….. ;
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
performed.
Examples:
select /*+ INDEX(emp_city idx_job_code) */ empname,
job_code from emp where job_code = ‘T’;
In the above example we
are querying the emp table to find employees who are Temporary (job_code = ‘T’)
in the organization.
The above approach will be
faster only if we know that less than 50% rows will be returned by the above
query. If we know that there are more Temporary employees than the Permanent (job_code
= ‘P’) employees, then the above approach will not be efficient. It is better
that we do a FULL Table scan.
DELETE /*+ INDEX(emp_status idx_emp_status)*/ FROM
emp_status WHERE status = ‘T’;
INDEX_ASC
/*+ INDEX_ASC(table index[index .. index]) */
INDEX_ASC
is almost the same as INDEX Hint. The difference is that if INDEX Range is
specified the entries are scanned in ascending order.
INDEX_DESC
/*+ INDEX_DESC (table index[index .. index]) */
INDEX_DESC
is almost the same as INDEX Hint. The difference is that if INDEX Range is
specified the entries are scanned in descending order.
FULL
You
can use the FULL hint to bypass the use of the INDEX. For example if you have a
table, which is indexed, and the value you are searching for has a large number
of duplicates, then you can go in for a Full Table scan. If an index is used,
in this case it will be inefficient. Using FULL hint will bypass the index(es).
Syntax:
/*+ FULL (table) */
Example:
select /*+ FULL(emp_status) */ empname, status from
emp_status where status = ‘P’;
NO_INDEX
The NO_INDEX hint explicitly
specifies which index cannot be used for the specified table.
Example:
select /*+ NO_INDEX(emp_status emp_status) */ empname,
status from emp_status where status = ‘P’;
-
If this hint specifies single or
multiple available index(es), then the optimizer does not consider a scan on
these indexes. Other indexes not specified are still considered. -
If this hint specifies no indexes,
then the optimizer does not consider a scan on any index on the table. This is
the same as a NO_INDEX
hint that specifies a list of all available indexes for the table.
Summary
Since you know more about your application and data, you
can pass on this information to Oracle to improve the performance of your
system.
By
using Hints, you can improve certain SQL statements that might otherwise be
inefficient.
