Oracle - Optimizer Hints

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/hints.htm
http://allthingsoracle.com/a-beginners-guide-to-optimizer-hints/ - done reading
http://www.dba-oracle.com/t_sql_hints_tuning.htm
http://www.oradev.com/hints.jsp
http://psoug.org/reference/hints.html
http://searchitchannel.techtarget.com/feature/Using-parallel-SQL-to-improve-Oracle-database-performance
http://stackoverflow.com/questions/421973/will-oracle-optimizer-use-multiple-hints-in-the-same-select

oracle

How can we specify hints?

The hint must appear after the DELETE, INSERT, SELECT, or UPDATE keyword, and must be in the form of a comment:

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint [text]] */

Also valid is the less fashionable syntax:

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint [text]]

No spaces are allowed between the comment delimiter and the plus sign.

What is the purpose of the MATERIALIZE hint?

WITH temp AS (  SELECT  /*+ materialize */  TO_CHAR (TO_DATE (PrdFrom, 'YYYYMM'), 'MON-YYYY')
         || ' to '
         || TO_CHAR (TO_DATE (PrdTo, 'YYYYMM'), 'MON-YYYY')
            DataPeriodDisplay,
         SampleName,
         SampleSize,
         A.CMIRRSAMPLEID,
         NVL (FNGETVTE_SUBPOP (UB.ICD9PDC,
                                   UB.ICD9ODC1,
                                   UB.ICD9ODC2,
                                   UB.ICD9ODC3,
                                   UB.ICD9ODC4,
                                   UB.ICD9ODC5,
                                   UB.ICD9ODC6,
                                   UB.ICD9ODC7,
                                   UB.ICD9ODC8,
                                   UB.ICD9ODC9,
                                   UB.ICD9ODC10,
                                   UB.ICD9ODC11,
                                   UB.ICD9ODC12,
                                   UB.ICD9ODC13,
                                   UB.ICD9ODC14,
                                   UB.ICD9ODC15,
                                   UB.ICD9ODC16,
                                   UB.ICD9ODC17,
                                   UB.ICD9ODC18,
                                   UB.ICD9ODC19,
                                   UB.ICD9ODC20,
                                   UB.ICD9ODC21,
                                   UB.ICD9ODC22,
                                   UB.ICD9ODC23,
                                   UB.ICD9ODC24,
                                   UB.ADMITDATE,
                                   UB.DOB,
                                   UB.STARTYEAR,
                                   UB.STARTMONTH,
                                   UB.DISCHARGEDATE),
                  'XX') STRATA
    FROM CMIRRSample A, CMUBIR UB
   WHERE     '2018' BETWEEN TO_CHAR (TO_DATE (PrdFrom, 'YYYYMM'), 'YYYY')
                        AND TO_CHAR (TO_DATE (PrdTo, 'YYYYMM'), 'YYYY')
         AND A.HCOID = 2001
         AND A.CMIRRSAMPLEID = UB.CMIRRSAMPLEID
         AND SampleType = 'HCOI'
         AND MEASURESET = 'VTE'
         )
SELECT DataPeriodDisplay, SampleName, SampleSize, CMIRRSAMPLEID FROM temp WHERE STRATA=3
ORDER BY UPPER (SampleName)

In the above code, without the materialize hint, if we run the sub-select inside the WITH clause, or remove the STRATA=3 from the where clause, it run really fast, but if we add the STRATA=3, it runs really slow. Without the materialize hint, the query takes about 33 seconds, but with the materialize hint, it takes about 147 milli-seconds. So, the materialize hint tell Oracle to actually create a temporary table or materialized view.

How can we instruct the optimizer to carry out a full table scan?

Here’s an example instructing that a full table scan should be carried out on the emp table:

SELECT /*+ FULL(emp) */
      name
  FROM emp
 WHERE job = 'SALESMAN';

Why should we avoid using hints?

  • If you must use hints, assume you’ve used them incorrectly. So don’t.
  • With every Oracle patch or upgrade, assume every hint is going to go wrong. So don’t.
  • With every DDL, assume every hint applied to that object is going to go wrong. So don’t.

The reason to be wary of hinting is that by embedding hints in your SQL, you are overriding the optimizer and saying that you know more than it does – not just now, but every time in the future that your SQL will be run, irrespective of any other changes that may happen to your database. The likely consequence of this is that your SQL will possibly run sub-optimally now and almost certainly in the future.

You can relax the rules slightly in your test environment, especially after massive data or structural changes may have thrown the optimizer temporarily off the scent. You may also experiment with hints when carrying out one-off operations. However, if you ever feel the need to include hints in your application, you should really think twice about it and see if there is any alternative solution.

How can I get more information on various hints?

  1. There are many dozens of different hints (have a look in the v$sql_hint view), however close to half of them are undocumented.
  2. Search the web for "oracle hints"

What is the purpose of the FIRST_ROWS(n) hint?

This hint instructs the optimizer to select a plan that returns the first n rows most efficiently.

SELECT /*+ FIRST_ROWS(10) */ empno, ename
FROM emp
WHERE deptno = 10;

What is the purpose of the FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100?

I DO NOT KNOW. NEED RESEARCH. NEED TO BREAK THIS QUESTION UP.

What is the purpose of the ALL_ROWS hint?

Instruct the optimizer to choose the plan that most effectively returns the resultset at the minimum cost.

What is the purpose of the NO_INDEX(<table_name> < index_name>) hint?

Instructs the optimizer to specifically not use the named index in determining a plan.

SELECT /*+ NO_INDEX(emp emp_ix) */ empno, ename
FROM emp, dept
WHERE emp.deptno = dept.deptno;

What is the purpose of the INDEX_COMBINE, INDEX_JOIN, INDEX_ASC and INDEX_FFS hints?

I DO NOT KNOW. NEED RESEARCH. NEED TO BREAK THIS QUESTION UP.

What is the purpose of the LEADING(table_name) hint?

This hint tells Oracle to use the parameterised table as the first in the join order. The optimizer will consequently select a join chain that starts with this table.

SELECT /*+ LEADING (dept) */ empno, ename
FROM emp, dept
WHERE emp.deptno = dept.deptno;

What is the purpose of the ORDERED hint?

Related to the LEADING hint is the ORDERED hint. This hint instructs Oracle to join tables in the exact order in which they are listed in the FROM clause.

What is the purpose of the CACHE(table) hint?

This hint tells Oracle to add the blocks retrieved for the table to the head of the most recently used list. This might be useful with regularly-used lookup tables, for example.

SELECT /*+ CACHE (d) */ deptno, dname
FROM dept d;

Oracle caches small tables by default, making this hint redundant in many cases. Also often redundant is the NOCACHE hint, since this places blocks at the tail of the LRU list, which is also Oracle’s default behaviour with the majority of blocks.

What is the purpose of the CARDINALITY(table n) hint?

This hint instructs Oracle to use n as the table, rather than rely on its own stats. You may need to use this hint with a global temporary table, for instance.

SELECT /*+ CARDINALITY (gtt, 1000) */ gtt.gtt_id, dname
FROM dept d, global_temp_tab gtt
WHERE d.deptno = gtt.deptno;

What is the purpose of the REWRITE hint?

This hint instructs Oracle to rewrite the query using a materialized view, irrespective of cost.

What is the purpose of the PARALLEL (table n) hint?

This hint tells the optimizer to use n concurrent servers for a parallel operation.

What is the purpose of the APPEND hint?

This hint instructs the optimizer to carry out a direct-path insert. This may make INSERT … SELECT statements faster because inserted data is simply appended to the end of the table and any referential constraints are ignored.

It is not the referential integrity that will be ignored during an attempt to direct path an insert but it is the direct path that will be silently ignored when a trigger or a referential integrity is present in the inserted table.

insert /*+ append */ into emp select * from t_emp;

What is the purpose of the RULE hint?

This hint basically turns off the optimizer. This hint has been deprecated and should not be used.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License