Databases Reference
In-Depth Information
RT_DATE")
filter("E1"."HIRE_DATE"="J"."START_DATE" AND "E1"."EMPLOYEE_ID"="J"."EMPL
OYEE_ID")
SQL>
In this example, the query includes additional hints besides the
INDEX
hint that specifies an index on the
table
EMPLOYEES
that you'd like the optimizer to use. The query also includes the
LEADING
hint to specify
the exact join order. The
USE_NL
and
USE_MERGE
hints specify the join method the database must use.
Adjusting the optimizer_index_cost_adj Parameter
You can influence the optimizer to use an index by adjusting the value of the
optimizer_index_cost_adj
initialization parameter. You can set this parameter at the system or session level. Here's an example
that shows how to set this parameter at the session level:
SQL> alter session set optimizer_index_cost_adj=50;
Session altered.
SQL>
The default value for the
optimizer_index_cost_adj
parameter is 100, and you can set the parameter to a
value between 0 and 10,000. The lower the value of the parameter, the more likely it is for the optimizer
to use an index.
The
optimizer_index_cost_adj
parameter lets you adjust the cost of an index access. The optimizer
uses a default value of 100 for this parameter, which means that it evaluates an indexed access path
based on the normal costing model. Based on the optimizer's estimate of the cost of performing an
indexed read, it makes the decision as to whether to use the index. Usually this works fine. However, in
some cases, the optimizer doesn't use an index even if it leads to a better execution plan because the
optimizer's estimates of the cost of the indexed access path may be off.
The optimizer uses a default value of 100 for the
optimizer_index_cost_adj
parameter, so you make
the index cost seem lower to the optimizer by setting this parameter to a smaller value. Any value less
than 100 makes the use of an index look cheaper (in terms of the cost of an indexed read) to the
optimizer. Often, when you do this, the optimizer starts using the index you want it to use. In this
example, you set the
optimizer_index_cost_adj
parameter to 50, making the cost of an index access path
appear half as expensive as its normal cost (100). The lower you set the value of this parameter, the
cheaper an index cost access path appears to the optimizer, and the more likely it will be to prefer an
index access path to a full table scan.
We recommend that you set the
optimizer_index_cost_adj
parameter only at the session level for a
specific query because the parameter has the potential to change the execution plans for many queries if
you set it at the database level. By default, if you set the
ALL_ROWS
optimizer goal, there's a built-in
preference for full table scans on part of the optimizer. By setting the
optimizer_index_cost_adj
parameter to a value less than 100, you're inducing the optimizer to prefer an index scan over a full table
scan. Use the
optimizer_index_cost_adj
parameter with confidence, especially in an OLTP environment
where you can experiment with low values such as 5 or 10 for the parameter in order to force the
optimizer to use an index.
By default, the optimizer assumes that the cost of a multiblock read I/O associated with a full table
scan and the single block read cost associated with an indexed read are identical. However, a single
block read is likely to be less expensive than a multiblock read. The
optimizer_index_cost_adj
parameter lets you adjust the cost of a single block read associated with an index read more accurately to
