Databases Reference
In-Depth Information
In step 6, we change the hint again, asking the optimizer to use indexes to access data on
the
CUSTOMERS
table, with the
/*+
INDEX(C)
*/
hint. The corresponding execution plan is
shown in the following screenshot. We can see that the optimizer has chosen the primary key
index
CUSTOMERS_PK
to answer our query, instead of the bitmap index used before.
In step 7, we disable the auto-trace functionality of SQL*Plus.
There's more...
We have seen how simple it is to hint the optimizer by inserting our hint in a comment starting
with a plus sign
/*+
our_hint
*/
.
Hints are used because we may know information about data that is unknown to the Oracle
optimizer. With the hint mechanism, we can provide this information to achieve a better
execution plan.
There are many different hints we can use, and here is a list of the ones most commonly used:
F
ALL_ROWS
: Informs the optimizer that we want to retrieve all the rows of the query
F
FIRST_ROWS
(n)
: Informs the optimizer that we are interested only in the first n
rows of the query
F
FULL
(table_name)
: Asks for full table access to the table
table_name
F
CLUSTER
(table_name)
: Instructs the optimizer to use a cluster scan when
accessing the specified table
F
HASH
(table_name)
: Allows access to a table in the hash cluster using a hash scan
