Oracle Optimizer: Moving to and working with CBO – Part 6

Previous
installments of this series have covered how the Cost Based Optimizer works and
behaves. We will now look at how to assist the optimizer to do its job
properly.

11. Hints

Hints
are exactly what it means–clues or directives that will assist the optimizer
in choosing an execution plan. Developers know more about the data they are
dealing with and Hints provides us with the option of guiding the Optimizer to
do things in a certain way–the way we would like a statement to run.

By
using hints, part of the Optimizer’s decision-making job is done by us.

A
few things to remember about Oracle Hints:

1.
Hints are not orders but directives to the optimizer.

2.
Hints are provided in comment format that is embedded in the query. A plus sign
is required at the beginning, right after the comment delimiter, for the
optimizer to accept it as a hint. No space is permitted between the comment
delimiter and the plus sign.

         
/*+ <hint> */
--+ <hint>

3.
Multiple hints can be provided in a single comment for a statement, each
separated with spaces.

4.
Hints are meant for DML statements: INSERT, UPDATE, DELETE and SELECT.

5.
Hints are not case sensitive.

6.
If a wrong or invalid hint is provided, the optimizer ignores it and continues
with the execution of the statement. The optimizer will not notify the user
about such hints.

7.
If multiple hints are provided that conflict with each other, the optimizer
will ignore the same.

8.
Comments are directly inserted in the SQL statements. These should be used with
caution, as the executions plans are likely to remain the same.

9.
Hints are CBO features. Using them in RBO setup will force the queries to run
in cost mode. The exception to this is the RULE hint that invokes the RBO for
executing a statement.

10.
Hints can be used to influence the mode of the optimizer, the access path, the
join order, the join method used etc..; this is mentioned in detail below.

11.
Hints can be local or global. Local hints are provided directly in the SQL
statements. Use global hints instead of embedding hints inside a view; this is
discussed below.

12.
SQL statements that are executed directly may behave differently when executed
from within PL/SQL. Make use of hints in such cases to achieve the required
results.

13.
Table hints can be provided with the table name. . If an alias name is
provided, use it instead of the table name.

14.
Avoid the use of schema name along with the table name in hints, even if they
appear in the FROM clause. Using aliases is a safe bet.

15.
As stated by Oracle Documentation, the use of hints involves extra code that
must be managed, checked and controlled. Use hints to tame queries that execute
with sub-optimal execution plans, but take care to provide the right access
paths.

Listed
below are some important hints that are widely used.

ALL_ROWS : for good throughput and resource utilization.
FIRST_ROWS : for good response time.
RULE : Use rule-based optimization rather than cost.
CHOOSE : Decide on rule or cost optimization based on the existence of statistics.
FULL : for doing a full table scan on the table.
HASH : hash scan, applies only for clustered tables (do not confuse with HASH join).
ROWID : table scan by rowid.
CLUSTER : cluster scan, applies only for clustered tables.
INDEX : index scan, specify the table and the index name.
INDEX_ASC : for range scan, scan index in ascending order of values.
INDEX_DESC : for range scan, scan index in descending order of values.
INDEX_JOIN : use index join as an access path.
Two indexes could be joined to return the required values.
INDEX_FFS : perform a fast full scan on the index rather than on the table.
NO_INDEX : avoid the use of the specified index or all indexes.
AND_EQUAL : merge 2-5 single column index scans to arrive at the output.
INDEX_COMBINE : explicitly choose a bitmap access path, make use of bitmap indexes.
ORDERED : access and join tables in the order mentioned in the FROM clause, left to right.
USE_NL : use Nested Loop for joining tables.
USE_HASH : use Hash joins.
USE_MERGE : use Sort-Merge joins.

The
optimizer hints ALL_ROWS, FIRST_ROWS, RULE and CHOOSE affect the Optimizer mode
for executing the query, irrespective of what is set at session level.

From
Oracle 9i, FIRST_ROWS can be optionally provided with the number of rows that
should be returned, e.g. FIRST_ROWS(10). Please note that this hint is ignored
for statements where Oracle needs to retrieve all rows for processing.

The RULE Hint causes the Optimizer to use rule based optimization
to choose the execution path. This is an instant solution for queries that ran
perfectly in RBO but have slowed down in CBO. If you have time constraints or are
unable to resolve performance issues in CBO, you may consider using this hint,
but my recommendation is that you come back to these queries later on and fine-tune
it to run without the hint.

The
access path hints for accessing data from table and indexes (FULL, INDEX_FFS,
INDEX, NO_INDEX, INDEX_COMBINE, INDEX_JOIN etc.) affect the choice of access
path by the optimizer. The table being affected could be specified along with
the hint. If an alias name is provided, use it instead of the table name.

e.g.:
improving a queries response time.

select /*+ first_rows */ trx_value
from   jnc_rtl_sales_iface_hdr
where  trx_no = 1211;

e.g.: Full table scan directive.

select /*+ full(a) */ a.shop_no, a.subinventory_code, b.item_code
from  jnc_shop_mapping a, jnc_rtl_sales_iface_hdr b
where b.shop_no = a.shop_no;

e.g.: Prevent use of some indexes.

select /*+ no_index(b jnc_catdtl_n2 jnc_catdtl_n1) */ *
from   jnc_purcat a, jnc_catdtl b
where  b.purcat = a.purcat
and    b.catnam = 'roger'
and    b.cat_id = 1;

e.g.: Direct use of indexes.

select /*+ index(a jnc_purcat_n1) full(b) */ *
from   jnc_purcat a, jnc_catdtl b
where  b.purcat = a.purcat
and    b.catnam = 'roger'
and    b.cat_id = 1;

Using Hints in Views

If
a query with hints is selecting data from a complex view, chances are that the
hints will be ignored if they do not propagate inside the view. Hints can be
provided in the query itself on which the view is based. In general, it is not
recommended to use hints in views as the data can be selected with different
conditions. You may use it if the need arises, but beware of how the data will
be selected from the view.

Oracle
recommends the use of Global hints instead of embedding hints in the view
itself. A global hint specifies the table name present in the view along with
the access path to be chosen. For example:

/*+ index(emp_v.emp emp_pk */

In
the above hint, EMP_V is the view name and EMP is the table name used in the
view.

e.g.: Make use of an index on a table present inside
the view.

SQL> create or replace view emp_v as
  2  select ename, dname
  3  from   emp, dept
  4  where  dept.deptno = emp.deptno;
  
View created.

SQL> select /*+ index(emp_v.emp emp_n2) */ * 
  2  from emp_v where dname = 'Dubai';

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles