Oracle Hints
Version 19c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Note The demos on this page are intended to show valid syntax but it is far easier to use a hint to make a mess of things than it is to improve things. So many, if not most, of the demos here are written intentionally to show that they can increase the cost. Before you proceed read everything in this General Information section.

Warning: Before you use this page, or any hint, it is critically important that you understand what you are about to do. The simple overriding fact is that Oracle Corp. has not documented the vast majority of hints and for those they have documented the documentation is wholly inadequate and often misleading or incomplete. Some of the worst SQL I have ever seen has been bad not because it was terribly written from the standpoint of syntax but rather it was written by someone with less than a whole clue about how the hints they embedded into that SQL that they likely found in some nonsense on the internet that referred to a previous version or a version that never existed except in their dreams.

In an attempt to educate my readers I am providing this link to Jonathan Lewis' "Rules for Hinting"
https://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/
as well as the following screen shot in case Jonathan ever does a house cleaning and removes the page.

If you think you know more about proper use of hints than Jonathan seek professional help from a board certified psychotherapist.
Advice I gladly provide to almost everyone that has used a hint in an example posted on the web.
Hint Sections
Access Mehod Hints (Cluster) In Memory Partitioning Hints
Access Method Hints (Table) Join Operations Hints Query Rewrite Hints
Adaptive Plan Hints Join Order Hints Query Transformation Hints
Append Hints Miscellaneous Hints Semantic Effect Hints
Bind Aware Hints Model Clause SemiJoin Hints
Cache Hints Online Application Upgrade Stats Hints
Container Database Optimization Approaches and Goals Hints Vector Hints
Cost Based Optimizer Optimizer Feature Enabling Hints XML Hints
General Purpose Parallel Hints  
Data Dictionary Objects
V$SQL_HINT    
Dictionary Demo col sql_feature format a35
col class format a30
col inverse format a30
col version format a8

SELECT class, name, sql_feature, inverse, version
FROM v$sql_hint
ORDER BY 1,2;
Fully Hinting comment by Jonathan Lewis on USENET Consider, for example:

SELECT /*+ index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;

For weeks, this may give you the plan:

   NESTED LOOP
     table access by rowid t1
       index range scan t1_abc
     table access by rowid t2
       index range scan t2_abc

Then, because of changes in statistics, or init.ora parameters, or nullity of a column, or a few other situations that may have slipped my mind at the moment, this might change to:

   HASH JOIN
      table access by rowid t2
        index range scan t2_abc
      table access by rowid t1
        index range scan t1_abc

Your hints are still obeyed, the plan has changed. On the other hand, if you had specified:

SELECT /*+ no_parallel(t1) no_parallel(t2) no_parallel_index(t1) no_parallel_index(t2)
ordered use_nl(t2) index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;

Then I think you could be fairly confident that there was no way that Oracle could obey the hints whilst changing the access path.

Morgan's comment:
Of course the above would only be true in the specific version Jonathan was thinking about when he wrote his statement. Try it in another version and all bets are off.
 
Access Method Hints (Cluster)
Each following hints described in this section suggests an access method for a cluster.
CLUSTER

Documented		 Explicitly requests a nested loop of the cluster index of one of the tables in the cluster.
/* CLUSTER([@queryblock] <tablespec>) */
conn uwclass/uwclass@pdbdev

CREATE CLUSTER sc_srvr_id (
srvr_id NUMBER(10))
SIZE 1024;

CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;

CREATE TABLE cservers
CLUSTER sc_srvr_id (srvr_id) AS
SELECT * FROM servers;

CREATE TABLE cserv_inst
CLUSTER sc_srvr_id (srvr_id) AS
SELECT * FROM serv_inst;

set autotrace traceonly exp

SELECT cs.latitude
FROM cservers cs, cserv_inst csi
WHERE cs.srvr_id = csi.srvr_id;

SELECT /*+ CLUSTER(cservers) */ cs.latitude
FROM cservers cs, cserv_inst csi
WHERE cs.srvr_id = csi.srvr_id;
CLUSTERING

Documented		 Valid only for INSERT and MERGE operations on tables that are enabled for attribute clustering. The CLUSTERING hint enables attribute clustering for direct-path inserts (serial or parallel). This results in partially-clustered data, that is, data that is clustered per each insert or merge operation. This hint overrides a NO ON LOAD setting in the DDL that created or altered the table.
/*+ CLUSTERING */
TBD
HASH

Documented		 Explicitly chooses a hash scan to access the specified table. Only applies to hash clusters.
/*+ HASH(<tablespec>) */
conn uwclass/uwclass@pdbdev

CREATE CLUSTER sthc_si (srvr_id NUMBER(10))
SIZE 1024 SINGLE TABLE HASHKEYS 11
TABLESPACE uwdata;

CREATE TABLE si_hash
CLUSTER sthc_si (srvr_id) AS
SELECT *
FROM serv_inst;

set autotrace traceonly explain

SELECT srvr_id
FROM si_hash
WHERE srvr_id = 503
GROUP BY srvr_id;

SELECT /*+ HASH(si_hash) */ srvr_id
FROM si_hash
WHERE srvr_id = 503
GROUP BY srvr_id;
NO_CLUSTERING

Documented		 valid only for INSERT and MERGE operations on tables that are enabled for attribute clustering. The NO_CLUSTERING hint disables attribute clustering for direct-path inserts (serial or parallel). This hint overrides a YES ON LOAD setting in the DDL that created or altered the table.
/*+ NO_CLUSTERING */
TBD
 
Access Method Hints (Table)
With the exception of the first two listings, each following hints described in this section suggests an access method for a table.
AND_EQUAL Undocumented
TBD
BITMAP_TREE Undocumented
TBD
FULL

Documented		 Explicitly chooses a full table scan for the specified table.
/*+ FULL(<tablespec>) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly explain

SELECT s.latitude
FROM servers s
WHERE s.srvr_id = 1;

SELECT /*+ FULL(servers) */ s.latitude
FROM servers s
WHERE s.srvr_id = 1;
INDEX

Documented		 Explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes. Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes because it is a more versatile hint.
/*+ INDEX([@queryblock] <tablespec> <index_name>) */
conn oe/oe@pdbdev

CREATE INDEX ix_customers_gender
ON customers(gender);

set autotrace traceonly explain

SELECT *
FROM customers c
WHERE c.gender = 'M';

SELECT /*+ INDEX(customers ix_customers_gender) */ *
FROM c.customers
WHERE c.gender = 'M';

SELECT /*+ INDEX_ASC(customers ix_customers_gender) */ *
FROM customers c
WHERE c.gender = 'M';

SELECT /*+ INDEX_DESC(customers ix_customers_gender) */ *
FROM customers c
WHERE c.gender = 'M';
INDEX_ASC

Documented		 Explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values.
/*+ INDEX_ASC([@queryblock] <tablespec> <index_name>) */
See INDEX Demo Above
INDEX_COMBINE

Documented		 Explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.
/*+ INDEX_DESC([@queryblock] <tablespec> <indexspec>) */
conn hr/hr@pdbdev

set autotrace traceonly explain

SELECT *
FROM employees e
WHERE (e.manager_id = 108) OR (e.department_id = 110);

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
FROM employees e
WHERE ((e.manager_id = 108) OR (e.department_id = 110));
INDEX_DESC

Documented		 Explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values.
/*+ INDEX_DESC([@queryblock] <tablespec> <indexspec>) */
See INDEX Demo Above
INDEX_FFS

Documented		 Causes a fast full index scan rather than a full table scan. Appears to be identical to INDEX_FFS_ASC.
/*+ INDEX_FFS([@queryblock] <tablespec> <indexspec>) */
See INDEX SCAN Demos Below
INDEX_JOIN

Documented		 Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
/*+ INDEX_JOIN([@queryblock] <tablespec> <indexspec>) */
conn oe/oe@pdbdev

set autotrace traceonly explain

SELECT e.department_id
FROM employees e
WHERE e.manager_id < 110
AND e.department_id < 50;

---------------------------------------------------------------------
| Id | Operation                    | Name              | Cost(%CPU)|
---------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   |    2  (0) |
|* 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |    2  (0) |
|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    1  (0) |
---------------------------------------------------------------------

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ e.department_id
FROM employees e
WHERE e.manager_id < 110
AND e.department_id < 50;

-------------------------------------------------------------
| Id | Operation           | Name              | Cost(%CPU) |
-------------------------------------------------------------
|  0 | SELECT STATEMENT    |                   |    3 (34)  |
|* 1 |  VIEW               | index$_join$_001  |    3 (34)  |
|* 2 |   HASH JOIN         |                   |            |
|* 3 |    INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    2 (50)  |
|* 4 |    INDEX RANGE SCAN | EMP_MANAGER_IX    |    2 (50)  |
-------------------------------------------------------------
INDEX_RS

Undocumented		 Instructs the optimizer to perform an index range scan for the specified table.
/*+ INDEX_RS([@queryblock] <tablespec> <indexspec>) */
TBD
INDEX_RS_ASC

Undocumented		 Instructs the optimizer to perform an index range scan for the specified table.
/*+ INDEX_RS([@queryblock] <tablespec> <indexspec>) */
TBD
INDEX_RS_DESC

Undocumented		 Instructs the optimizer to perform an index range scan for the specified table.
/*+ INDEX_RS([@queryblock] <tablespec> <indexspec>) */
conn oe/oe@pdbdev

set autotrace traceonly explain

SELECT c.customer_id
FROM customers c
WHERE c.customer_id BETWEEN 100 and 120;

SELECT /*+ INDEX_RS_DESC(c customers_pk) */ c.customer_id
FROM customers c
WHERE c.customer_id BETWEEN 100 and 120;

set autotrace off

SELECT /*+ INDEX_RS_DESC(c customers_pk) */ c.customer_id
FROM customers c
WHERE c.customer_id BETWEEN 100 and 120;
INDEX_SS

Documented		 Instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.
/*+ INDEX_SS([@queryblock] <tablespec> <indexspec>) */
See INDEX SCAN Demos Below
INDEX_SS_ASC

Documented		 Instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition. Each parameter serves the same purpose as in "INDEX Hint".

The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index. This hint does not change the default order of the index, and therefore does not specify anything more than the INDEX_SS hint. However, you can use the INDEX_SS_ASC hint to specify ascending range scans explicitly should the default behavior change.
/*+ INDEX_SS_ASC([@queryblock] <tablespec> <indexspec>) */
See INDEX SCAN Demos Below
INDEX_SS_DESC

Documented		 Instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan and the index is ascending, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition. For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order.
/*+ INDEX_SS_DESC([@queryblock] <tablespec> <indexspec>) */
See INDEX SCAN Demos Below
NLJ_BATCHING Undocumented
TBD
NLJ_PREFETCH Undocumented
TBD
NO_INDEX

Documented		 Explicitly disallows a set of indexes for the specified table. The NO_INDEX hint applies to function-based, B*tree, bitmap, cluster, or domain indexes.
/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly explain

SELECT s.latitude
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX(i ix_serv_inst) */ s.latitude
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX(i pk_serv_inst) */ s.latitude
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
NO_INDEX_FFS

Documented		 Instructs the optimizer to exclude a fast full index scan of the specified indexes.
/*+ NO_INDEX_FFS([@queryblock] <tablespec> <indexspec>) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly exp

SELECT s.latitude
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX_FFS(i pk_serv_inst) NO_INDEX_FFS(i ix_serv_inst) */ s.latitude
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
NO_INDEX_RS Undocumented: Instructs the optimizer to exclude an index range scan of the specified indexes.
/*+ NO_INDEX_RS([@queryblock] <tablespec> <indexspec>) */
conn hr/hr@pdbdev

col column_name format a30

SELECT uic.column_position, uic.column_name
FROM user_ind_columns uic
WHERE uic.index_name = 'EMP_NAME_IX';

set autotrace traceonly explain

SELECT e.first_name
FROM employees e
WHERE e.last_name BETWEEN 'A' AND 'B';

SELECT /*+ NO_INDEX_RS(e emp_name_ix) */ e.last_name
FROM employees e
WHERE e.first_name BETWEEN 'A' AND 'B';
NO_INDEX_SS

Documented		 Instructs the optimizer to exclude a skip scan of the specified indexes.
/*+ NO_INDEX_SS([@queryblock] <tablespec> <indexspec>) */
TBD
NO_NLJ_BATCHING Undocumented but clearly related to No Nested Loop Batching
TBD
NO_NLJ_PREFETCH Undocumented but clearly related to No Nested Loop Join Prefetch
TBD
Index Scan Demos

Also see the link at page bottom		 conn hr/hr@pdbdev

col column_name format a30

SELECT uic.column_position, uic.column_name
FROM user_ind_columns uic
WHERE uic.index_name = 'EMP_NAME_IX';

set autotrace traceonly explain

SELECT e.last_name
FROM employees e;

SELECT /*+ INDEX_FFS(e emp_name_ix) */ e.last_name
FROM employees e;

SELECT /*+ INDEX_SS(e emp_name_ix) */ e.last_name
FROM employees e;

SELECT /*+ INDEX_SS_ASC(e emp_name_ix) */ e.last_name
FROM employees e;

SELECT /*+ INDEX_DESC(e emp_name_ix) */ e.last_name
FROM employees e;
 
Adaptive Plan Hints
ADAPTIVE_PLAN /*+ ADAPTIVE_PLAN */
TBD
NO_ADAPTIVE_PLAN /*+ NO_ADAPTIVE_PLAN */
TBD
 
Append Hints
APPEND and NOAPPEND

Documented		 Instructs the optimizer to use direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

When you use the APPEND hint for INSERT, data is simply appended to a table above the HWM which has the effect of not creating UNDO. Existing free space in blocks is not used.
/*+ APPEND */
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT *
FROM servers
WHERE 1=2;

INSERT /*+ NO_APPEND */ INTO t
SELECT * FROM servers;

SELECT COUNT(*) FROM t;

INSERT INTO t
SELECT * FROM servers;

SELECT COUNT(*) FROM t;

INSERT /*+ APPEND */ INTO t
SELECT * FROM servers;

SELECT COUNT(*) FROM t;

COMMIT;

SELECT COUNT(*) FROM t;
APPEND_VALUES

Documented		 Forces the optimizer to use direct-path INSERT with the VALUES clause. If you do not specify this hint, then conventional INSERT is used. This hint is only supported with the VALUES clause of the INSERT statement. If you specify it with an insert that uses the subquery syntax it is ignored.
/*+ APPEND_VALUES */
conn uwclass/uwclass@pdbdev

CREATE TABLE t (
testcol VARCHAR2(3));

EXPLAIN PLAN FOR
INSERT INTO t
VALUES
('XYZ');

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
INSERT /*+ APPEND_VALUES */ INTO t
VALUES
('XYZ');

SELECT * FROM TABLE(dbms_xplan.display);
NOAPPEND

Documented		 /*+ NOAPPEND */
 
TBD
 
Bind Aware
These two undocumented hints relate to the awareness, in cursor sharing, of the value(s) bound to the cursor through bind variables
BIND_AWARE /*+ BIND_AWARE */
TBD
NO_BIND_AWARE /*+ NO_BIND_AWARE */
TBD
 
Cache Hints
AV_CACHE

New: 18.1		 Undocumented
TBD
CACHE

Documented		 Instructs the optimizer to place the blocks retrieved for the table at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This hint is useful for small lookup tables.
/*+ CACHE([@queryblock] <tablespec>) */
conn hr/hr@pdbdev

set autotrace traceonly exp

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name
FROM employees hr_emp;

-- there is no change visible in the trace
CACHE_TEMP_TABLE /*+ CACHE_TEMP_TABLE */
TBD
NOCACHE

Documented		 Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
/*+ NOCACHE([@queryblock] <tablespec>]) */
See CACHE Demo Above
 
Container Database Hints
CONTAINERS

Documented		 Use in a SELECT statement in a multitenant container database (CDB) to specify that the statement contains the CONTAINERS() clause. Such a statement lets you query data in the specified table or view across all containers in a CDB or application container.
/*+ CONTAINERS (DEFAULT_PDB_HINT = '<hint'>') */
SELECT /*+ CONTAINERS(DEFAULT_PDB_HINT='NO_PARALLEL') */ (
  CASE WHEN COUNT(*) < 10000 THEN 'Less than 10,000' ELSE '10,000 or more' END) "TABLE COUNT"
FROM CONTAINERS(dba_tables);
PDB_LOCAL_ONLY Undocumented
TBD
 
Cost Based Optimizer Hints
BITMAP Undocumented
TBD
BUFFER Undocumented
TBD
BYPASS_UJVC Undocumented
TBD
CACHE_CB Undocumented
TBD
CLUSTER_BY_ROWID and
NO_CLUSTER_BY_ROWID		 Undocumented
TBD
CUBE_GB Undocumented
TBD
DML_UPDATE Undocumented
TBD
DOMAIN_INDEX_FILTER Undocumented
TBD
DOMAIN_INDEX_NO_SORT Undocumented
TBD
DOMAIN_INDEX_SORT Undocumented
TBD
EXPR_CORR_CHECK Undocumented
TBD
FBTSCAN Undocumented
TBD
FULL_OUTER_JOIN_TO_OUTER and
NO_FULL_OUTER_JOIN_TO_OUTER		 Undocumented
TBD
HWM_BROKERED Undocumented
TBD
INDEX_RRS Undocumented: Internal hint that can only be inserted by the optimizer
TBD
LOCAL_INDEXES Undocumented
TBD
MERGE_CONST_ON Undocumented
TBD
NO_BUFFER Undocumented
TBD
NO_DOMAIN_INDEX_FILTER Undocumented
TBD
NO_PARTIAL_COMMIT Undocumented
TBD
NO_QKN_BUFF Undocumented
TBD
NO_REF_CASCADE Undocumented
TBD
NUM_INDEX_KEYS Undocumented but appears likely to specify the number of index keys if stats information is known to be misleading
TBD
ORDERED_PREDICATES Undocumented
TBD
OUTER_JOIN_TO_ANTI and
NO_OUTER_JOIN_TO_ANTI		 Undocumented
TBD
OVERFLOW_NOMOVE Undocumented
TBD
QUEUE_CURR Undocumented
TBD
QUEUE_ROWP Undocumented
TBD
REF_CASCADE_CURSOR Undocumented
TBD
RESTORE_AS_INTERVALS Undocumented
TBD
ROWID Undocumented
TBD
SAVE_AS_INTERVALS Undocumented
TBD
SKIP_EXT_OPTIMIZER Undocumented
TBD
SKIP_UNQ_UNUSABLE_IDX Undocumented
TBD
SQLLDR Undocumented
TBD
STREAMS Undocumented
TBD
SUBQUERY_PRUNING and
NO_SUBQUERY_PRUNING		 Undocumented
TBD
SWAP_JOIN_INPUTS and
NO_SWAP_JOIN_INPUTS		 Undocumented
TBD
SYS_DL_CURSOR Undocumented
TBD
SYS_PARALLEL_TXN Undocumented
TBD
USE_ANTI Undocumented
TBD
X_DYN_PRUNE Undocumented
TBD
 
General Purpos Hints
DRIVING_SITE

Documented		 Forces query execution to be done at a user selected site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.
/*+ DRIVING_SITE([@queryblock] <tablespec>) */
SELECT p1.first_name, p2.first_name, p2.last_name
FROM person p1, person@mlib_user p2
WHERE p1.person_id = p2.person_id
AND p1.first_name <> p2.first_name;

SELECT /*+ DRIVING_SITE(p1) AAA */ p1.first_name, p2.first_name, p2.last_name
FROM person p1, person@mlib_user p2
WHERE p1.person_id = p2.person_id
AND p1.first_name <> p2.first_name;

SELECT sql_text, remote
FROM v$sql
WHERE sql_text LIKE '%AAA%';

SELECT /*+ DRIVING_SITE(p2) BBB */ p1.first_name, p2.first_name, p2.last_name
FROM person p1, person@mlib_user p2
WHERE p1.person_id = p2.person_id
AND p1.first_name <> p2.first_name;

SELECT sql_text, remote
FROM v$sql
WHERE sql_text LIKE '%BBB%';
DYNAMIC_SAMPLING_EST_CDN Undocumented: Forces cardinality estimation for analyzed tables.
*/+ dynamic_sampling_est_cdn(<query_block>) */
SELECT /*+ dynamic_sampling(e 1) dynamic_sampling_est_cdn(e) */ COUNT(*)
FROM hr.employees e
WHERE e.commission_pct > 0.3;
MONITOR

Documented		 Forces real-time SQL monitoring for the query, even if the statement is not long running. This hint is valid only when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.
/*+ MONITOR */
SELECT value
FROM v$parameter
WHERE name = 'control_management_pack_access';

SELECT /*+ MONITOR */ COUNT(*)
FROM user_tables;
NO_MONITOR

Documented		 Disables real-time SQL monitoring for the query, even if the query is long running.
/*+ NO_MONITOR */
-- this SQL statement is made intentionally long running
SELECT /*+ NO_MONITOR */ COUNT(*)
FROM dba_segments s, dba_extents e
WHERE s.owner = e.owner;
NO_PX_JOIN_FILTER

Documented		 Prevents the optimizer from using parallel join bitmap filtering.
/*+ NO_PX_JOIN_FILTER(<tablespec>) */
TBD
NO_RESULT_CACHE

Documented		 The optimizer caches query results in the result cache if the RESULT_CACHE_MODE initialization parameter is set to FORCE. In this case, the NO_RESULT_CACHE hint disables such caching for the current query.
/*+ NO_RESULT_CACHE */
conn uwclass/uwclass@pdbdev

SELECT /*+ NO_RESULT_CACHE */ srvr_id
FROM (
  SELECT srvr_id, SUM(cnt) SUMCNT
  FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
  GROUP BY srvr_id)
WHERE sumcnt = 2;
PX_JOIN_FILTER

Documented		 Forces the optimizer to use parallel join bitmap filtering.
/*+ PX_JOIN_FILTER(<tablespec>) */
TBD
QB_NAME

Documented		 Use the QB_NAME hint to define a name for a query block. This name can then be used in a hint in the outer query or even in a hint in an inline view to affect query execution on the tables appearing in the named query block.

If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block. Query blocks that are not named using this hint have unique system-generated names. These names can be displayed in the plan table and can also be used in hints within the query block, or in query block hints.
/*+ QB_NAME(<query_block_name>) */
conn hr/hr@pdbdev

set autotrace traceonly exp

SELECT employee_id, last_name
FROM employees e
WHERE last_name = 'Smith';

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
FROM employees e
WHERE last_name = 'Smith';

QB Demo:
There is a very important lesson about the use of named query blocks well illustrated by a query posted in the OTN forums and Jonathan Lewis's response. First here's the query

UPDATE (
  SELECT /*+ QB_NAME(MAIN_EDR) USE_HASH(@SUBQ_MMT mmt0) LEADING (rec0 mmt0)
             INDEX_RS(@SUBQ_MMT mmt0 PSP_MONEY_MOVEMENT_TRANSAC_FK3)
             INDEX_RS(rec0 PSP_ENTRY_DETAIL_RECORD_I2 ) UNNEST(@SUBQ_MMT) MERGE(@MAIN_EDR)
         */
         rec0.ENTRY_DETAIL_RECORD_SEQ, rec0.trace_number, rec0.N_A_C_H_A_FILE_FK,
         rec0.RECORD_DATA,   rec0.VERSION, rec0.MODIFIER_ID, rec0.MODIFIED_DATE
  FROM PSP_ENTRY_DETAIL_RECORD rec0
  WHERE rec0.N_A_C_H_A_FILE_FK IS NULL
  AND rec0.initiation_date = :p_offload_date
  AND rec0.N_A_C_H_A_FILE_TYPE = :p_nacha_file_type
  AND EXISTS (
              SELECT /*+ qb_name(SUBQ_MMT) */ 'T'
              FROM PSP_MONEY_MOVEMENT_TRANSACTION mmt0
              WHERE REC0.MONEY_MOVEMENT_TRANSACTION_FK = MMT0.MONEY_MOVEMENT_TRANSACTION_SEQ
              AND mmt0.offload_batch_fk = :p_offload_batch_id)
  ORDER BY rec0.LEGAL_NAME, rec0.COMPANY_fk, REC0.N_A_C_H_A_BATCH_TYPE, REC0.RECORD_DATA,
           rec0.AMOUNT, rec0.ENTRY_DETAIL_RECORD_SEQ) src
SET trace_number = DECODE(NVL(record_data,'0'),'0', NULL, seq_trace_number.NEXTVAL),
    N_A_C_H_A_FILE_FK = :v_nacha_file_id,
    VERSION = VERSION + 1,
    MODIFIER_ID = :p_user_id,
    MODIFIED_DATE = :v_utc_date;

Note how query_block SUBQ_MMT is names in the EXISTS subquery. The issue brought up in the forum was that the hint "was not working."

Also posted was this:

SQL> SELECT name, hint FROM user_outline_hints;

NAME HINT
----  ----------
EDR   USE_HASH(@"SEL$798CEC2A" "MMT0"@"SUBQ_MMT")
EDR   LEADING(@"SEL$798CEC2A" "REC0"@"MAIN_EDR" "MMT0"@"SUBQ_MMT")
EDR   INDEX_RS_ASC(@"SEL$798CEC2A" "MMT0"@"SUBQ_MMT" ("PSP_MONEY_MOVEMENT_TRANSACTI
EDR   INDEX_RS_ASC(@"SEL$798CEC2A" "REC0"@"MAIN_EDR" ("PSP_ENTRY_DETAIL_RECORD"."IN
EDR   OUTLINE(@"MAIN_EDR")
EDR   OUTLINE(@"UPD$1")
EDR   OUTLINE(@"SUBQ_MMT")
EDR   MERGE(@"MAIN_EDR")
EDR   OUTLINE(@"SEL$0E53DE07")
EDR   UNNEST(@"SUBQ_MMT")
EDR   OUTLINE_LEAF(@"SEL$798CEC2A")

Here is Jonathan's response:
You have two hints that are directed explicitly to a query block called subq_mmt - but after transformation that query block doesn't exist (so the hints are effectively meaningless), the query block SEL$798CEC2A has appeared instead from the unnesting and merging.

The resulting query block is derived from the names of the query blocks that generated it, and is deterministic, so it's safe to use it in your own hints. So in your leading(), use_hash, and index_rs_asc hint you need to include @SEL$798CEC2A and then you should get the result you want.
RESULT_CACHE

Documented		 Instructs the database to cache the results of the current query or query fragment in memory and then to use the cached results in future executions of the query or query fragment. The hint is recognized in the top-level query, the subquery_factoring_clause, or FROM clause inline view. The cached results reside in the result cache memory portion of the shared pool.
/*+ RESULT_CACHE */
conn uwclass/uwclass@pdbdev

SELECT /*+ RESULT_CACHE */ srvr_id
FROM (
  SELECT srvr_id, SUM(cnt) SUMCNT
  FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
  GROUP BY srvr_id)
WHERE sumcnt = 2;
 
In Memory Hints
Each of the following hints relates to the In-Memory Option
INMEMORY

Documented		 Enables in-memory queries
/*+ INMEMORY( [@QUERYBLOCK)] <tablespec>) */
TBD
INMEMORY_PRUNING

Documented		 Enables pruning of in-memory queries
/*+ INMEMORY_PRUNING( [@QUERYBLOCK)] <tablespec>) */
TBD
NO_INMEMORY

Documented		 Disables in-memory queries
/*+ NO_INMEMORY( [@QUERYBLOCK)] <tablespec>) */
TBD
NO_INMEMORY_PRUNING

Documented		 Disables pruning of in-memory queries
/*+ NO_INMEMORY_PRUNING( [@QUERYBLOCK)] <tablespec>) */
TBD
 
Join Operations Hints
Each hint described in this section suggests a table join operation.

Join methods:

In the loop join algorithm, an outer loop is formed that is composed of a few entries that are to be selected. Then, for each entry in the outer loop, a look-up is performed for matching entries, in the inner loop.

In the merge join algorithm, both tables are accessed in the same order. If there's a sorted index on the matching column, on both tables, then no sorting is needed. All we have to do is read the rows in the order presented by the index. The reason it's called a merge join is that the algorithm, in detail, looks much like the algorithm for merging two (sorted) data streams together.

Let's say we got two tables, ORDERS and ORDER_ITEMS. Let's say we have sorted indexes on ORDER_NUMBER on both tables. Naturally, the index on ORDERS can forbid duplicates, while the index on ORDER_ITEMS has to permit duplicates.

Now, in this case, which algorithm is faster? It depends.

Let's say we want to look up a single order. This happens in OLTP systems a lot. The loop join is probably faster. The outer loop will find a single order number, and that means the inner loop will have to probe the index on ORDER_ITEMS just once. This is true even if we have to scan the order table, based on CUSTOMER_ID and ORDER_DATE.

Now let's say we want a report for all the reports, with details for April. The merge join is probably faster. With hundreds of orders to process, walking the index on ORDER_ITEMS once beats the heck out of doing hundreds of probes.
CUBE_AJ Undocumented
TBD
FACTORIZE_JOIN and
NO_FACTORIZE_JOIN		 Undocumented
TBD
HASH_AJ Undocumented
TBD
MERGE_AJ Undocumented
TBD
NATIVE_FULL_OUTER_JOIN

Documented		 Instructs the optimizer to use native full outer join, which is a native execution method based on a hash join. I can't imagine why anyone would think this a good idea but here it is.
/*+ NATIVE_FULL_OUTER_JOIN */
TBD
NL_AJ Undocumented
TBD
NO_NATIVE_FULL_OUTER_JOIN

Documented		 Instructs the optimizer to exclude the native execution method when joining each specified table. Instead, the full outer join is executed as a union of left outer join and anti-join.
/*+ NO_NATIVE_FULL_OUTER_JOIN */
TBD
NO_USE_BAND

Documented		 Instructs the optimizer to exclude band joins when joining each specified table to another row source.
/*+ NO_USE_BAND ([@queryblock)] <tablespec>) */
SELECT /*+ NO_USE_BAND(e1 e2) */ e1.last_name || ' has salary between 100 less and 100 more than ' || e2.last_name AS "SALARY COMPARISON"
FROM employees e1, employees e2
WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100;
NO_USE_CUBE

Documented		 Instructs the optimizer to exclude cube joins when joining each specified table to another row source using the specified table as the inner table.
/*+ NO_USE_CUBE ([@queryblock)] <tablespec>) */
TBD
NO_USE_HASH

Documented		 Instructs the optimizer to exclude hash joins when joining each specified table to another row source using the specified table as the inner table.
/*+ NO_USE_HASH([@queryblock] <tablespec> <tablespec>) */
TBD
NO_USE_MERGE

Documented		 Instructs the optimizer to exclude sort-merge joins when joining each specified table to another row source using the specified table as the inner table.
/*+ NO_USE_MERGE([@queryblock] <tablespec> <tablespec>) */
conn hr/hr@pdbdev

set autotrace traceonly explain

SELECT *
FROM employees e, departments d
WHERE e.department_id = d.department_id;

SELECT /*+ NO_USE_MERGE(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
NO_USE_NL

Documented		 Instructs the optimizer to exclude nested loops joins when joining each specified table to another row source using the specified table as the inner table.
/*+ NO_USE_NL([@queryblock] <tablespec> <tablespec>) */
conn oe/oe@pdbdev

set autotrace traceonly explain

SELECT *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;

SELECT /*+ NO_USE_NL(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;
OUTER_JOIN_TO_INNER and
NO_OUTER_JOIN_TO_INNER		 Undocumented
TBD
PARTIAL_JOIN and
NO_PARTIAL_JOIN		 Undocumented
TBD
PUSH_JOIN_PRED

Undocumented		 Force pushing of a join predicate into the view (found in the 8.1.5 docs)
/*+ PUSH_JOIN_PRED (<tablespec>) */
SELECT /*+ PUSH_JOIN_PRED(v) */ T1.X, V.Y
FROM T1 (
  SELECT T2.X, T3.Y
  FROM T2, T3
  WHERE T2.X = T3.X) v
WHERE t1.x = v.x
AND t1.y = 1;
USE_BAND

Documented		 Instructs the optimizer to join each specified table with another row source using a band join.
/*+ USE_BAND ([@queryblock)] <tablespec>) */
SELECT /*+ USE_BAND(e1 e2) */ e1.last_name || ' has salary between 100 less and 100 more than ' || e2.last_name AS "SALARY COMPARISON"
FROM employees e1, employees e2
WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100;
USE_CUBE

Documented		 When the right-hand side of the join is a cube, the USE_CUBE hint instructs the optimizer
to join each specified table with another row source using a cube join. If the optimizer
decides not to use the cube join based on statistical analysis, then you can use
USE_CUBE to override that decision
/*+ USE_CUBE ([@queryblock)] <tablespec>) */
TBD
USE_HASH

Documented		 Causes Oracle to join each specified table with another row source using a hash join.
/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT /*+ USE_HASH (s i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT /*+ USE_MERGE (s i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
USE_MERGE

Documented		 Causes Oracle to join each specified table with another row source with a sort-merge join.
/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */
See USE_HASH Demo Above
USE_MERGE_CARTESIAN Undocumented: Causes Oracle to join each specified table with another row source using a merge Cartesian join.
TBD
USE_NL

Documented		 Causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. It stops the optimizer from evaluating the cost of a merge join or hash join - at a specific set of branch points in the CBO.
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id+0 = i.srvr_id+0;

SELECT /*+ USE_NL(i s) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id+0 = i.srvr_id+0;
USE_NL_WITH_INDEX

Documented		 Instructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table.
/*+ USE_NL_WITH_INDEX([@queryblock] <tablespec> <index_name>) */
conn oe/oe@pdbdev

set autotrace traceonly explain

SELECT *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;

SELECT /*+ USE_NL_WITH_INDEX(l item_order_ix) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;
 
Join Order Hints
The hints in this section suggest join orders:
LEADING

Documented		 Instructs the optimizer to use the specified set of tables as the prefix in the execution plan.
/*+ LEADING([@queryblock] <table_name> <table_name>) */
conn hr/hr@pdbdev

set autotrace traceonly explain

SELECT *
FROM employees e, departments d, job_history j
WHERE e.department_id = d.department_id
AND e.hire_date = j.start_date;

SELECT /*+ LEADING(e j) */ *
FROM employees e, departments d, job_history j
WHERE e.department_id = d.department_id
AND e.hire_date = j.start_date;
ORDERED

Documented		 Causes Oracle to only join tables in the order in which they appear in the FROM clause.
/*+ ORDERED */
conn oe/oe@pdbdev

set autotrace traceonly explain

SELECT o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = 'Mastroianni'
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

SELECT /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = 'Mastroianni'
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;
 
Miscellaneous Hints
ANSI_REARCH and
NO_ANSI_REARCH		 Undocumented
TBD
AUTO_REOPTIMIZE and
NO_AUTO_REOPTIMIZE		 Undocumented
TBD
BYPASS_RECURSIVE_CHECK See metalink for bug #1816154
TBD
BATCH_TABLE_ACCESS_BY_ROWID and
NO_BATCH_TABLE_ACCESS_BY_ROWID		 Undocumented
TBD
BITMAP_AND Undocumented
TBD
BUSHY_JOIN and
NO_BUSHY_JOIN		 Undocumented
TBD
COALESCE_SQ and
NO_COALESCE_SQ		 Undocumented
TBD
COLLECTIONS_GET_REFS Undocumented
TBD
CPU_COSTING and
NO_CPU_COSTING		 Disables/Enables Explain Plan cpu costing - the default is enabled
EXPLAIN PLAN FOR
SELECT srvr_id FROM serv_inst
MINUS
SELECT srvr_id FROM servers;

Explained.

SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |  999 |  4560 |    6   (34) | 00:00:01 |
|  1 |  MINUS                  |              |      |       |             |          |
|  2 |   SORT UNIQUE           |              |  999 |  3996 |    4   (25) | 00:00:01 |
|  3 |    INDEX FAST FULL SCAN | PK_SERV_INST |  999 |  3996 |    3    (0) | 00:00:01 |
|  4 |   SORT UNIQUE NOSORT    |              |  141 |   564 |    2   (50) | 00:00:01 |
|  5 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |    1    (0) | 00:00:01 |
---------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT /*+ NO_CPU_COSTING */ srvr_id FROM serv_inst
MINUS
SELECT /*+ NO_CPU_COSTING */ srvr_id FROM servers;

Explained.

SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |  999 |  4560 |  154 |
|  1 |  MINUS                  |              |      |       |      |
|  2 |   SORT UNIQUE           |              |  999 |  3996 |   78 |
|  3 |    INDEX FAST FULL SCAN | PK_SERV_INST |  999 |  3996 |    2 |
|  4 |   SORT UNIQUE NOSORT    |              |  141 |   564 |   76 |
|  5 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |    1 |
---------------------------------------------------------------------

Note
-----
- cpu costing is off (consider enabling it)
CURRENT_INSTANCE Undocumented
TBD
DATA_SECURITY_REWRITE_LIMIT and
NO_DATA_SECURITY_REWRITE_LIMIT		 Undocumented
TBD
DATA_VALIDATE Undocumented
TBD
DB_VERSION
First Available:11.1.0.6		 Undocumented
TBD
DECORRELATE and
NO_DECORRELATE		 Undocumented
TBD
DEREF_NO_REWRITE Undocumented
TBD
DST_UPGRADE_INSERT_CONV and
NO_DST_UPGRADE_INSERT_CONV		 Undocumented
TBD
ELIMINATE_JOIN Undocumented: First Available 10.2.0.1
See NO_ELIMINATE_JOIN below
ELIMINATE_OBY Undocumented
TBD
ELIM_GROUPBY and
NO_ELIM_GROUPBY		 Undocumented
TBD
EXPAND_TABLE and
NO EXPAND_TABLE		 Undocumented
TBD
FORCE_SAMPLE_BLOCK Undocumented
TBD
FRESH_MV

Documented		 When querying a real-time materialized view this hint instructs the optimizer to use on-query computation to fetch up-to-date data from the
materialized view, even if the materialized view is stale.

The optimizer ignores this hint in SELECT statement blocks that query an object that is not a real-time materialized view, and in all UPDATE, INSERT, MERGE, and DELETE statement blocks.
/*+ FRESH_MV */
TBD
GBY_PUSHDOWN Undocumented
TBD
GROUPING

Documented		 Use with data mining scoring functions when scoring partitioned models. This hint results in partitioning the input data set into distinct data slices so that each partition is scored in its entirety before advancing to the next partition. Parallelism by partition is still available. Data slices are determined by the partitioning key columns that were used when the model was built. This method can be used with any data mining function against a partitioned model. The hint may yield a query performance gain when scoring large data that is associated with many partitions, but may negatively impact performance when scoring large data with few partitions on large systems. Typically, there is no performance gain if you use this hint for single row queries.
/*+ GROUPING */
SELECT PREDICTION(/*+ GROUPING */my_model USING *) pred
FROM data_lake;
IGNORE_ON_CLAUSE Undocumented
TBD
IGNORE_OPTIM_EMBEDDED_HINTS Undocumented
TBD
IGNORE_WHERE_CLAUSE

Undocumented		 The following does not work so this hint is not as obvious as it might, at first, seem.
/*+ IGNORE_WHERE_CLAUSE */
SELECT /*+ IGNORE_WHERE_CLAUSE */ COUNT(*)
FROM servers
WHERE srvr_id LIKE '%5%';
INCLUDE_VERSION Undocumented
TBD
JSON_LENGTH (new 19c) Undocumented
TBD
MEMOPTIMIZE_WRITE Undocumented: Relates to the Memoptimize Pool introduced in version 18c.
TBD
NESTED_TABLE_FAST_INSERT Undocumented
TBD
NESTED_TABLE_GET_REFS Undocumented
TBD
NESTED_TABLE_SET_SETID Undocumented
TBD
NESTED_TABLE_SET_REFS Undocumented
TBD
NO_ACCESS Undocumented
TBD
NO_BIND_AWARE Undocumented
TBD
NO_CARTESIAN Undocumented
TBD
NO_CONNECT_BY_COMBINE_SW Undocumented
TBD
NO_CONNECT_BY_FILTERING Undocumented
TBD
NO_ELIMINATE_JOIN

Undocumented		 First Available 10.2.0.1
/*+ NO_ELIMINATE_JOIN(<table_alias>) */
SELECT /*+ gather_plan_statistics optimizer_features_enable('10.2.0.4') */ *
FROM (
  SELECT /*+ qb_name(v2) */ raw_sql_.business_entity_id, raw_sql_.business_entity_name,
  raw_sql_.owner_id, raw_sql_.address_id, rownum raw_rnum_
  FROM (
    SELECT /*+ qb_name(v1) NO_ELIMINATE_JOIN(b1) */ *
    FROM businessentities b1
    WHERE (b1.business_entity_id IN (
      SELECT /*+ qb_name(in) */ b2.business_entity_id
      FROM businessentities b2
      WHERE (business_entity_id = 'OWNER') OR (owner_id = 'ALL_IN_ALL')
      AND business_entity_id NOT IN (
        SELECT /*+ qb_name(not_in) */ r.business_entity_id
        FROM busentityroles r)))
  WHERE rownum <= 5
  ORDER BY business_entity_id asc) raw_sql_)
WHERE raw_rnum_ > 0;
NO_FILTERING Undocumented
TBD
NO_LOAD Undocumented
TBD
NO_MONITORING
Undocumented		 Likely a legacy version of NO_MONITOR from version 8.0.
TBD
NO_PUSH_JOIN_PRED

Undocumented		 Prevents pushing of a join predicate into a view.
/*+ NO_PUSH_JOIN_PRED (<tablespec>) */
TBD
NO_SQL_TUNE Undocumented
TBD
NO_SUBSTRB_PAD Undocumented
TBD
NO_USE_INVISIBLE_INDEXES Instructs the optimizer not to use invisible indexes even if they are available in the session
/*+ NO_USE_INVISIBLE_INDEXES */
conn uwclass/uwclass@pdbdev

CREATE TABLE invis AS
SELECT table_name, tablespace_name
FROM all_tables;

CREATE INDEX ix_invis
ON invis(table_name)
INVISIBLE;

set autotrace on

ALTER SESSION SET "optimizer_use_invisible_indexes" = TRUE;

SELECT table_name
FROM invis
WHERE table_name = 'SERVERS';

SELECT /*+ NO_USE_INVISIBLE_INDEXES */ table_name
FROM invis
WHERE table_name = 'SERVERS';
NO_ZONEMAP

Documented		 Disables the use of a zone map for different types of pruning. This hint overrides an ENABLE PRUNING setting in the DDL that created or altered the zone map.

v$sql_hint shows an officially undocumented inverse hint named ZONEMAP hint which we have included below.
/*+ NO_ZONEMAP ( [@queryblock] <tablespec> <JOIN | PARTITION | SCAN>) */
TBD
OUTLINE Undocumented
TBD
OUTLINE_LEAF Undocumented
TBD
PARTIAL_ROLLUP_PUSHDOWN and
NO_PARTIAL_ROLLUP_PUSHDOWN		 Undocumented
TBD
PIV_GB Undocumented
TBD
PIV_SSF Undocumented
TBD
PLACE_DISTINCT and
NO_PLACE_DISTINCT		 Undocumented
TBD
PLACE_GROUP_BY and
NO_PLACE_GROUP_BY		 Undocumented
TBD
PRECOMPUTE_SUBQUERY Undocumented
TBD
PRESERVE_OID Undocumented
TBD
PUSH_HAVING_TO_GBY and
NO_PUSH_HAVING_TO_GBY		 Undocumented
TBD
QUARANTINE (new 19c) Enables SQL Quarantine operations: See the DBMS_SQLQ built-in package. Only available on Exadata with Enterprise Edition.
Use DBMS_SQLDIAG.CREATE_SQL_PATCH to insert this hint into application compiled SQL.
TBD
RBO_OUTLINE Undocumented
TBD
REMOTE_MAPPED Undocumented
TBD
RESERVOIR_SAMPLING Undocumented
TBD
RESTRICT_ALL_REF_CONS Undocumented
TBD
SCN_ASCENDING Undocumented
TBD
SET_TO_JOIN and
NO_SET_TO_JOIN		 Undocumented
TBD
SKIP_PROXY Undocumented
TBD
SQL_SCOPE Undocumented
TBD
SUPPRESS_LOAD Undocumented
TBD
SYS_RID_ORDER Undocumented
TBD
TABLE_LOOKUP_BY_NL and
NO_TABLE_LOOKUP_BY_NL		 Undocumented
TBD
TIV_GB Undocumented
TBD
TIV_SSF Undocumented
TBD
TRACING Undocumented
TBD
USE_DAGG_UNION_ALL_GSETS and
NO_USE_DAGG_UNION_ALL_GSETS		 Undocumented
TBD
USE_HASH_AGGREGATION and
NO_USE_HASH_AGGREGATION		 Undocumented
TBD
USE_HASH_GBY_FOR_DAGGPSHD and
NO_USE_HASH_GBY_FOR_DAGGPSHD		 Undocumented
TBD
USE_HASH_GBY_FOR_PUSHDOWN and
NO_USE_HASH_GBY_FOR_PUSHDOWN		 Undocumented
TBD
USE_INVISIBLE_INDEXES

Documented but not in the Hints documentation		 Instructs the optimizer to consider the user of invisible indexes.
/*+ USE_INVISIBLE_INDEXES */
conn uwclass/uwclass@pdbdev

CREATE TABLE invis AS
SELECT table_name, tablespace_name
FROM all_tables;

CREATE INDEX ix_invis
ON invis(table_name)
INVISIBLE;

set autotrace on

SELECT table_name
FROM invis
WHERE table_name = 'SERVERS';

SELECT /*+ USE_INVISIBLE_INDEXES */ table_name
FROM invis
WHERE table_name = 'SERVERS';
USE_WEAK_NAME_RESL Undocumented
TBD
WITH_PLSQL Undocumented
TBD
XDB_FASTPATH_INSERT and NO_XDB_FASTPATH_INSERT Undocumented
TBD
ZONEMAP

Undocumented		 Introduced in version 12.1.0.1 with NO_ZONEMAP which is documented
/*+ ZONEMAP ( [@queryblock] <tablespec> <JOIN | PARTITION | SCAN>) */
TBD
 
Model Claus Hints
MODEL_COMPILE_SUBQUERY Undocumented
TBD
MODEL_DONTVERIFY_UNIQUENESS Undocumented
TBD
MODEL_DYNAMIC_SUBQUERY Undocumented
TBD
MODEL_MIN_ANALYSIS

Documented		 Instructs the optimizer to omit some compile-time optimizations of spreadsheet rules—primarily detailed dependency graph analysis. Other spreadsheet optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis can be lengthy if the number of spreadsheet rules is more than several hundreds.
/*+ MODEL_MIN_ANALYSIS */
TBD
MODEL_NO_ANALYSIS Undocumented
TBD
MODEL_PUSH_REF Undocumented
TBD
NO_MODEL_PUSH_REF Undocumented
TBD
 
Optimization Approaches and Goals Hints
Mode hints were introduced in version 8.1.0 and none have related inverse hints.
ALL_ROWS

Documented		 The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
/*+ ALL_ROWS */
conn / as sysdba

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer%';

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;

set autotrace traceonly explain

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;
CHOOSE /*+ CHOOSE */
TBD
FIRST_ROWS(n)

Documented

As of 12c Adaptive Execution Plans are disabled by this hint		 The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make these choices:
  • If an index scan is available, the optimizer may choose it over a full table scan.
  • If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
  • If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.
  • The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following: UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, FOR UPDATE, aggregating function and the DISTINCT operator.
/*+ FIRST_ROWS(<integer>) */
set autotrace trace exp

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

-- the differences are subtle so look closely
RULE

Undocumented

Deprecated		 Disables the Cost Based optimizer. This hint is not supported and should not be used. We are not sure if it is still active but include it here for backward compatibility.
/*+ RULE */
set autotrace trace exp

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
 
Optimizer Feature Enabling Hints
CARDINALITY
Undocumented
Deprecated		 Instructs the optimizer to use the provided integer as the computed cardinality of the table, or pipelined table function, without checking.
/*+ CARDINALITY(<tablespec>, <integer>) */
Superceded by the OPT_ESTIMATE hint
CURSOR_SHARING_EXACT

Documented		 Oracle can replace literals in SQL statements with bind variables, when it is safe to do so. This replacement is controlled with the CURSOR_SHARING initialization parameter. The CURSOR_SHARING_EXACT hint instructs the optimizer to switch this behavior off. In other words, Oracle executes the SQL statement without any attempt to replace literals with bind variables.
/*+ CURSOR_SHARING_EXACT */
conn / as sysdba

ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH;

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

-- as the client run two forced SQL statements
SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;
SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

-- as SYS look in the shared pool
set linesize 121
col sql_text format a50

SELECT address, child_address, sql_text, sql_id
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';

SELECT /*+ CURSOR_SHARING_EXACT */ latitude FROM uwclass.servers WHERE srvr_id = 3;

SELECT address, child_address, sql_text, sql_id
FROM gv$sql
WHERE sql_fulltext LIKE '%uwclass%';
DYNAMIC_SAMPLING

Documented		 The DYNAMIC_SAMPLING hint instructs the optimizer how to control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes.

You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify tablespec. The integer value is 0 to 10, indicating the degree of sampling. For pipelined table functions the range of values is 2 to 10.

Force dynamic sampling of tables where statistics do not exist such as Global Temporary Tables.

If the table is aliased the alias name, not the table name must be used.
/*+ DYNAMIC_SAMPLING([@queryblock] [<tablespec>] <integer>) */
conn uwclass/uwclass@pdbdev

CREATE TABLE ds AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

CREATE INDEX ds_objtype
ON ds(object_type);

SELECT object_type, COUNT(*)
FROM ds
GROUP BY object_type;

set autotrace trace exp

SELECT object_name
FROM ds
WHERE object_type = 'JAVA CLASS';

SELECT /*+ DYNAMIC_SAMPLING(ds 0) */ object_name
FROM ds
WHERE object_type = 'JAVA CLASS';

SELECT /*+ DYNAMIC_SAMPLING(ds 4) */ object_name
FROM ds
WHERE object_type = 'JAVA CLASS';

SELECT /*+ DYNAMIC_SAMPLING(ds 9) */ object_name
FROM ds
WHERE object_type = 'JAVA CLASS';
OPTIMIZER_FEATURES_ENABLE

Documented		 Acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle Database release number. It can be used tocheck for plan regressions after database upgrades. Specify the release number as an argument to the hint.
/*+ optimizer_features_enable('<version_number>') */
SELECT /*+ OPTIMIZER_FEATURE_ENABLE('11.1.0.7') */ latitude, longitude
FROM uwclass.servers
WHERE srvr_id = 5;
OPT_ESTIMATE

Undocumented

Supercedes the CARDINALITY hint		 Adjusts the CBO's default scaling factor.
/*+ OPT_ESTIMATE(table, <alias>, scale_rows=<scaling_factor>) */
conn hr/hr@pdbdev

CREATE OR REPLACE TYPE employees_t AUTHID DEFINER AS OBJECT (
employee_id   NUMBER(6),
first_name    VARCHAR2(20),
last_name     VARCHAR2(25),
department_id NUMBER(4));
/

CREATE OR REPLACE TYPE employees_tt AS TABLE OF employees_t;
/

CREATE OR REPLACE FUNCTION employees_ptf RETURN employees_tt
AUTHID CURRENT_USER PIPELINED IS
BEGIN
  FOR r IN (SELECT employee_id, first_name, last_name, department_id FROM employees) LOOP
    FOR i IN 1 .. 200 LOOP
      PIPE ROW (employees_t(r.employee_id, r.first_name, r.last_name, r.department_id));
    END LOOP;
  END LOOP;
  RETURN;
END employees_ptf;
/

SELECT COUNT(*) FROM TABLE(employees_ptf);

EXPLAIN PLAN FOR
SELECT *
FROM departments d, TABLE(employees_ptf) e
WHERE d.department_id = e.department_id;

SELECT * FROM TABLE(dbms_xplan.display);

-- calculate the scaling factor
SELECT 21400/8168 FROM dual;

EXPLAIN PLAN FOR
SELECT /*+ OPT_ESTIMATE(table, e, scale_rows=2.62) */ *
FROM departments d, TABLE(employees_ptf) e
WHERE d.department_id = e.department_id;

SELECT * FROM TABLE(dbms_xplan.display);
OPT_PARAM

Documented		 Lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED. For example, the following hint sets the parameter STAR_TRANSFORMATION_ENABLED to TRUE for the statement to which it is added.
/*+ OPT_PARAM(parameter_name, parameter_value) */
conn uwclass/uwclass@pdbdev

SELECT name, value
FROM v$parameter
WHERE name LIKE 'optimizer_index%';

SELECT /*+ OPT_PARAM('optimizer_index_cost_adj' '42') */ *
FROM servers;
 
Parallel Hints
The NOPARALLEL and SHARED were introduced in 8.1.0. NO_PARALLEL was introduced in 10.1.0.3 as a replacement for NOPARALLEL to confirm with the hint naming convention. The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.
CIV_GB

Documented		 Internal hint related to parallel query
/*+ CIV_GB */
TBD
DISABLE_PARALLEL_DML

Documented		 Disables parallel DML for DELETE, INSERT, MERGE, and UPDATE statements. Can be used to disable parallel DML for an individual
statement when parallel DML is enabled for the session with the ALTER SESSION ENABLE PARALLEL DML statement.
/*+ DISABLE_PARALLEL_DML */
TBD
DIST_AGG_PROLLUP_PUSHDOWN Undocumented
TBD
ENABLE_PARALLEL_DML

Documented		 Enables parallel DML for DELETE, INSERT, MERGE, and UPDATE statements. You can use this hint to enable parallel DML for an individual statement, rather than enabling parallel DML for the session with the ALTER SESSION ENABLE PARALLEL DML statement.
/*+ ENABLE_PARALLEL_DML */
TBD
NO_DIST_AGG_PROLLUP_PUSHDOWN Undocumented
TBD
NO_PARALLEL and NOPARALLEL

Documented		 Overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.
/*+ NO_PARALLEL([@queryblock] <tablespec>) */
conn hr/hr@pdbdev

CREATE TABLE employees_demo
PARALLEL (DEGREE 4) AS
SELECT * FROM employees;

SELECT table_name, degree
FROM user_tables;

set autotrace traceonly exp

SELECT last_name
FROM employees_demo hr_emp;

SELECT /*+ NO_PARALLEL(hr_emp) */ last_name
FROM employees_demo hr_emp;
NO_PARALLEL_INDEX and NO_PARALLEL_INDEX

Documented		 Override a PARALLEL attribute setting on an index. In this way you can avoid a parallel index scan operation.
/*+ NO_PARALLEL_INDEX([@queryblock] <tablespec> <index_name>) */
TBD
NO_PQ_CONCURRENT_UNION

Documented		 Instructs the optimizer to disable concurrent processing of UNION and UNION ALL operations.
/*+ NO_PQ_CONCURRENT_UNION[(@queryblock)] */
TBD
NO_PQ_EXPAND_TABLE (new 19c) Undcoumented
TBD
NO_PQ_REPLICATE Undocumented
TBD
NO_PQ_SKEW

Documented		 Advises the optimizer that the distribution of the values of the join keys for a parallel join is not skewed—that is, a high percentage of rows do not have the same join key values. The table specified in tablespec is the probe table of the hash join
/*+ NO_PQ_SKEW( */[@queryblock] <tablespec>) */
TBD
NO_STATEMENT_QUEUING

Documented		 Influences whether or not a statement is queued with parallel statement queuing. When PARALLEL_DEGREE_POLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue. A statement that bypasses the statement queue can potentially cause the system to exceed the maximum number of parallel execution servers defined by the value of the PARALLEL_SERVERS_TARGET initialization parameter, which determines the limit at which parallel statement queuing is initiated.
There is no guarantee that the statement that bypasses the parallel statement queue receives the number of parallel execution servers requested because only the number of parallel execution servers available on the system, up to the value of the PARALLEL_MAX_SERVERS initialization parameter, can be allocated.
/*+ NO_STATEMENT_QUEUING */
SELECT /*+ NO_STATEMENT_QUEUING */ s.server_name, i.location_name
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
 NO_USE_SCALABLE_GBY_INVDIST (new 19c) Undocumented
TBD
PARALLEL

Documented		 Specifies the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion. If any parallel restrictions are violated, the hint is ignored. The hint will (roughly speaking) make the optimizer divide a critical value by the value specified – at a specific set of points in the CBO.
/*+ PARALLEL([@queryblock] <tablespec> <degree | DEFAULT>) */
conn hr/hr@pdbdev

set autotrace traceonly exp

SELECT last_name
FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 2) */ last_name
FROM employees hr_emp;

-- overrides table definition and uses init parmameter
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name
FROM employees hr_emp;
PARALLEL_INDEX

Documented		 Specify the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.
/*+ PARALLEL_INDEX([@queryblock] <tablespec> <index_name> <degree | DEFAULT>) */
TBD
PQ_CONCURRENT_UNION

Documented		 Instructs the optimizer to enable concurrent processing of UNION and UNION ALL operations.
/*+ NO_PQ_CONCURRENT_UNION[(@queryblock)] */
TBD
PQ_DISTRIBUTE

Documented		 Improve parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.

Outer_distribution is the distribution for the outer table.
Inner_distribution is the distribution for the inner table.
Distribution values are: NONE, PARTITION, RANDOM, RANDOM_LOCAL
/*+ PQ_DISTRIBUTE([@queryblock] <tablespec> <distribution>) */
/*+ PQ_DISTRIBUTE([@queryblock] <tablespec> <outer_distribution><inner_distribution>) */
conn uwclass/uwclass@pdbdev

CREATE /*+ PQ_DISTRIBUTE(airplanes_hashpart, PARTITION) */ TABLE airplanes_hashpart
NOLOGGING PARALLEL 16
PARTITION BY HASH (program_id) PARTITIONS 11 AS
SELECT * FROM airplanes;
PQ_DISTRIBUTE_WINDOW Undocumented
TBD
PQ_EXPAND_TABLE (new 19c) Undocumented
TBD
PQ_FILTER

Documented		 Instructs the optimizer on how to process rows when filtering correlated subqueries.
/*+ PQ_FILTER(<HASH | NONE | RANDOM | SERIAL>) */
TBD
PQ_MAP Undocumented
TBD
PQ_NOMAP Undocumented
TBD
PQ_REPLICATE Undocumented
TBD
PQ_SKEW

Documented		 Advises the optimizer that the distribution of the values of the join keys for a parallel join is highly skewed—that is, a high percentage of rows have the same join key values. The table specified in tablespec is the probe table of the hash join.
/*+ PQ_SKEW([@queryblock] tablespec) */
TBD
PX_FAULT_TOLERANCE and
NO_PX_FAULT_TOLERANCE		 Undocumented
TBD
SHARED Undocumented
TBD
STATEMENT_QUEUING

Documented		 Influences whether or not a statement is queued with parallel statement queuing. When PARALLEL_DEGREE_POLICY is not set to AUTO, this hint enables a statement to be considered for parallel statement queuing, but to run only when enough parallel processes are available to run at the requested DOP. The number of available parallel execution servers, before queuing is enabled, is equal to the difference between the number of parallel execution servers in use and the maximum number allowed in the system, which is defined by the PARALLEL_SERVERS_TARGET initialization parameter.
/*+ STATEMENT_QUEUING */
SELECT /*+ STATEMENT_QUEUING */ s.server_name, si.install_location
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
USE_SCALABLE_GBY_INVDIST (new 19c) xx
TBD
 
Partitioning Hints
REORDER_WIF and
NO_REORDER_WIF		 Undocumented
TBD
USE_HIDDEN_PARTITIONS Undocumented
TBD
NO_USE_PARTITION_WISE_DISTINCT Undocumented
TBD
NO_USE_PARTITION_WISE_GBY Undocumented
TBD
NO_USE_PARTITION_WISE_WIF Undocumented
TBD
USE_PARTITION_WISE_DISTINCT Undocumented
TBD
USE_PARTITION_WISE_GBY Undocumented
TBD
USE_PARTITION_WISE_WIF Undocumented
TBD
 
Query Rewrite Hints
The rewrite hints were introduced in two batches. REWRITE and NO_REWRITE in 8.1.5 and the remainder in 10.1.0.3.
CHECK_ACL_REWRITE Undocumented
TBD
NO_BASETABLE_MULTIMV_REWRITE Undocumented
TBD
NO_COST_XML_QUERY_REWRITE Undocumented
TBD
NO_MULTIMV_REWRITE Undocumented
TBD
NO_PULL_PRED Undocumented
TBD
NO_PUSH_PRED

Documented		 Instructs the optimizer not to push a join predicate into the view.
/*+ NO_PUSH_PRED(<@queryblock> | <[@queryblock> <tablespec>]) */
conn hr/hr@pdbdev

set autotrace traceonly exp

SELECT *
FROM employees e, (
  SELECT manager_id
  FROM employees) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
FROM employees e, (
  SELECT manager_id
  FROM employees) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;
NOREWRITE and NO_REWRITE

Documented		 Use on any query block of a request. This hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED.
/*+ NO_REWRITE(<@queryblock>) */
conn sh/sh@pdbdev

set autotrace traceonly explain

SELECT SUM(s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

SELECT /*+ NO_REWRITE */ SUM(s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;
OLD_PUSH_PRED Undocumented
TBD
OR_EXPAND and NO_OR_EXPAND Undocumented
TBD
PULL_PRED Undocumented
TBD
PUSH_PRED

Documented		 Instructs the optimizer to push a join predicate into the view.
/*+ PUSH_PRED(<@queryblock> | <[@queryblock> <tablespec>]) */
conn hr/hr@pdbdev

set autotrace trace exp

SELECT *
FROM employees e, (
  SELECT manager_id
  FROM employees) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
FROM employees e, (
  SELECT manager_id
  FROM employees) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;
REWRITE

Documented		 Use with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, Oracle uses that view regardless of its cost. Oracle does not consider views outside of the list. If you do not specify a view list, Oracle searches for an eligible materialized view and always uses it regardless of its cost.
/*+ REWRITE([@queryblock] <view, view, ...>) */
conn uwclass/uwclass@pdbdev

CREATE MATERIALIZED VIEW mv_rewrite
TABLESPACE uwdata
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

set autotrace traceonly exp

SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND s.srvr_id = 502
GROUP BY s.srvr_id, i.installstatus;

SELECT /*+ REWRITE */ s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND s.srvr_id = 502
GROUP BY s.srvr_id, i.installstatus;
REWRITE_OR_ERROR Undocumented
TBD
 
Query Transformation Hints
CONNECT_BY_CB_WHR_ONLY Undocumented
TBD
CONNECT_BY_COST_BASED Undocumented
TBD
CONNECT_BY_COMBINE_SW Undocumented
TBD
CONNECT_BY_ELIM_DUPS Undocumented
TBD
CONNECT_BY_FILTERING Undocumented but found in an AWR report as shown.
SELECT /*+ connect_by_filtering */ privilege#, level
FROM sysauth$ CONNECT BY grantee#=prior privilege#
AND privilege#>0
START WITH grantee#=:1 AND privilege#>0;
EXPAND_GSET_TO_UNION Undocumented
TBD
FACT

Documented		 In the context of the star transformation instructs the optimizer that the table specified be considered a fact table.
/*+ FACT([@queryblock] <tablespec>) */
TBD
GBY_CONC_ROLLUP Undocumented: Likely related to GROUP BY and ROLLUP
TBD
INLINE Undocumented: If you want to control the optimiser, then the 'materialize' hint makes it create a temporary table; the 'inline' hint makes it perform 'macro-substitution'.
~ Jonathan Lewis
TBD
LIKE_EXPAND Undocumented
TBD
MATERIALIZE Undocumented: If you want to control the optimiser, then the 'materialize' hint makes it create a temporary table; the 'inline' hint makes it perform 'macro-substitution'.
~ Jonathan Lewis
TBD
MERGE

Documented		 The MERGE hint lets you merge views in a query. If a view's query block contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
/*+ MERGE(<@queryblock> [tablespec]); */
conn hr/hr@pdbdev

set autotrace traceonly exp

SELECT e1.last_name, e1.salary, v.avg_salary
FROM employees e1, (
  SELECT department_id, AVG(salary) avg_salary
  FROM employees e2
  GROUP BY department_id) v
WHERE e1.department_id = v.department_id
AND e1.salary > v.avg_salary

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
FROM employees e1, (
  SELECT department_id, AVG(salary) avg_salary
  FROM employees e2
  GROUP BY department_id) v
WHERE e1.department_id = v.department_id
AND e1.salary > v.avg_salary;
MV_MERGE Undocumented
TBD
NO_CHECK_ACL_REWRITE Undocumented
TBD
NO_CONNECT_BY_CB_WHR_ONLY Undocumented
TBD
NO_CONNECT_BY_COST_BASED Undocumented
TBD
NO_CONNECT_BY_ELIM_DUPS Undocumented
TBD
NO_EXPAND

Documented		 Prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or INLISTS in the WHERE clause. Normally, the optimizer would consider using OR expansion and use this method if it decides the cost is lower than not using it.
/*+ NO_EXPAND(<@queryblock>);
conn oe/oe@pdbdev

set autotrace traceonly explain

SELECT *
FROM employees e, departments d
WHERE e.manager_id = 108
OR d.department_id = 110;

SELECT /*+ NO_EXPAND */ *
FROM employees e, departments d
WHERE e.manager_id = 108
OR d.department_id = 110;
NO_EXPAND_GSET_TO_UNION Undocumented
TBD
NO_FACT

Documented		 Used in the context of the star transformation. Instruct the optimizer that the queried table should not be considered as a fact table.
/*+ NO_FACT([@queryblock] <tablespec>) */
TBD
NO_MERGE

Documented		 Instructs the optimizer not to combine the outer query and any inline view queries into a single query. This hint makes the optimizer bypass some code that would otherwise transform a query using the rules of complex view merging.
/*+ NO_MERGE(<@queryblock> [tablespecification]); */
conn hr/hr@pdbdev

set autotrace traceonly explain

SELECT e1.last_name, seattle_dept.department_name
FROM employees e1,
(SELECT location_id, department_id, department_name
FROM departments
WHERE location_id = 1700) seattle_dept
WHERE e1.department_id = seattle_dept.department_id;

SELECT /*+ NO_MERGE(seattle_dept) */ e1.last_name, seattle_dept.department_name
FROM employees e1, (
  SELECT location_id, department_id, department_name
  FROM departments
  WHERE location_id = 1700) seattle_dept
WHERE e1.department_id = seattle_dept.department_id;
NO_ORDER_ROLLUPS Undocumented
TBD
NO_PRUNE_GSETS Undocumented
TBD
NO_PUSH_SUBQ

Documented		 Instructs the optimizer to evaluate nonmerged subqueries as the last step in the execution plan. Doing so can improve performance if the subquery is relatively expensive or does not reduce the number of rows significantly.
/*+ NO_PUSH_SUBQ(<@queryblock>) */
TBD
NO_QUERY_TRANSFORMATION

Documented		 Instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite.
/*+ NO_QUERY_TRANSFORMATION) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly explain

SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM servers
  MINUS
  SELECT srvr_id
  FROM serv_inst);

SELECT /*+ NO_QUERY_TRANSFORMATION */ DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM servers
  MINUS
  SELECT srvr_id
  FROM serv_inst);
NO_STAR_TRANSFORMATION

Documented		 Instructs the optimizer not to perform star query transformation.
/*+ NO_STAR_TRANSFORMATION(<@queryblock>) */
TBD
NO_UNNEST

Documented		 Turns off subqueries unnesting for the current statement.
/*+ NO_UNNEST(<@queryblock>) */
conn uwclass/uwclass@pdbdev

set autotrace traceonly explain

SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT /*+ UNNEST */ srvr_id FROM serv_inst);

-------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Cost (%CPU) |
-------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    5   (20) |
|* 1 |  HASH JOIN SEMI        |              |   11 |    5   (20) |
|  2 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |    1    (0) |
|  3 |   INDEX FAST FULL SCAN | PK_SERVERS   |  999 |    3    (0) |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("SRVR_ID"="SRVR_ID")

SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT /*+ NO_UNNEST */ srvr_id FROM serv_inst);

---------------------------------------------------------------
| Id | Operation          | Name         | Rows | Cost (%CPU) |
---------------------------------------------------------------
|  0 | SELECT STATEMENT   |              |    1 |  128    (0) |
|* 1 |  INDEX FULL SCAN   | PK_SERVERS   |    7 |    1    (0) |
|* 2 |   INDEX FULL SCAN  | PK_SERV_INST |    2 |    2    (0) |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SERV_INST"
           "SERV_INST" WHERE "SRVR_ID"=:B1))
2 - access("SRVR_ID"=:B1)
    filter("SRVR_ID"=:B1)
OPAQUE_TRANSFORM Undocumented
TBD
OPAQUE_XCANONICAL Undocumented
TBD
PRECOMPUTE_SUBQUERY Undocumented: Tanel Poder's blog entry on this hint is the best on the web so here's the link. Below I have just copied from Tanel an example of the proper usage of this hint from his blog. [Click Here]
SELECT a
FROM t1
WHERE a IN (
  SELECT /*+ PRECOMPUTE_SUBQUERY */ b
  FROM t2);
PUSH_SUBQ

Documented.		 Instructs the optimizer to evaluate nonmerged subqueries at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then evaluating the subquery earlier can improve performance.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.
/*+ PUSH_SUBQ(<@queryblock>) */
TBD
STAR_TRANSFORMATION

Documented

STAR, from version 8.1 is deprecated.		 Instructs the optimizer to use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

When specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only if it seems reasonable to do so. If no subqueries are generated, then there is no transformation and the best plan for the untransformed query is used, ignoring the hint.
/*+ STAR_TRANSFORMATION[(<@queryblock>)] */
conn sh/sh@pdbdev

set autotrace traceonly exp

SELECT *
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND p.prod_status = 'obsolete';

SELECT /*+ STAR_TRANSFORMATION */ *
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND p.prod_status = 'obsolete';
TRANSFORM_DISTINCT_AGG and
NO_TRANSFORM_DISTINCT_AGG		 Undocumented
TBD
UNNEST

Documented		 Instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.
/*+ UNNEST(<@queryblock>) */
See NO_UNNEST Demo Above
USE_CONCAT

Documented		 Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

The USE_CONCAT hint turns off inlist processing and OR-expands all disjunctions, including inlists.
/*+ USE_CONCAT */
conn hr/hr@pdbdev

set autotrace traceonly explain

SELECT *
FROM employees e
WHERE manager_id = 108
OR department_id = 110;

SELECT /*+ USE_CONCAT */ *
FROM employees e
WHERE manager_id = 108
OR department_id = 110;
USE_TTT_FOR_GSETS Undocumented
TBD
 
Semantic-Effect Hints also known as Online Application Upgrade Hints
CHANGE_DUPKEY_ERROR_INDEX

Documented		 This hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-00001.
CHANGE_DUPKEY_ERROR_INDEX(<table_name>,<index_name>);
CHANGE_DUPKEY_ERROR_INDEX(<table_name>,(<column_name>,<column_name>));
CREATE TABLE t (
testcol VARCHAR2(20));

ALTER TABLE t
ADD CONSTRAINT uc_t_testcol
UNIQUE (testcol);

INSERT INTO t (testcol) VALUES ('A');
INSERT INTO t (testcol) VALUES ('A');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T,TESTCOL) */ INTO t (testcol) VALUES ('A');
IGNORE_ROW_ON_DUPKEY_INDEX

Documented		 This hint applies only to single-table INSERT operations. It causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.
IGNORE_ROW_ON_DUPKEY_INDEX(table, index);
IGNORE_ROW_ON_DUPKEY_INDEX(table, (column_list));
CREATE TABLE t (
testcol NUMBER(2));

ALTER TABLE t
ADD CONSTRAINT uc_t_testcol
UNIQUE (testcol);

INSERT INTO t (testcol) VALUES (1);
INSERT INTO t (testcol) VALUES (7);
INSERT INTO t (testcol) VALUES (1);

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(T,UC_T_TESTCOL)) */ INTO t
(testcol) VALUES (1);

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(T,(TESTCOL)) */ INTO t
(testcol) VALUES (1);

INSERT INTO t (testcol) VALUES (1);

SELECT * FROM t;

BEGIN
  FOR i IN 1..10 LOOP
    INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(T,(TESTCOL)) */ INTO t
    (testcol) VALUES (i);
  END LOOP;
END;
/

SELECT * FROM t;
RETRY_ON_ROW_CHANGE

Documented

This demo is a modified version of Rob van Wijk. I wrote my own demo, not much different from Rob's. But the truth is that Rob's is better so I have made a few small changes but essentially what you see to the right is what Rob wrote. I often produce copies of valuable examples when I have concerns that they might some day not still be available.		 This hint is valid only for UPDATE and DELETE operations. It is not supported for INSERT or MERGE. When specified the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.
-- demo setup
CREATE TABLE t (
rid NUMBER(5),
col NUMBER(5) NOT NULL);

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid);

INSERT /*+ APPEND */ INTO t
SELECT level, level
FROM dual
CONNECT BY LEVEL <= 50000;

CREATE TABLE logtable (
last_id                 NUMBER(5),
number_of_processed_ids NUMBER(5));

INSERT INTO logtable VALUES (NULL,0);

CREATE OR REPLACE PROCEDURE p(pid IN t.rid%TYPE) AUTHID DEFINER IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE logtable
  SET last_id = pid,
    number_of_processed_ids = number_of_processed_ids + 1;
  COMMIT;
END p;
/

CREATE OR REPLACE TRIGGER aut_t
AFTER UPDATE
ON T
FOR EACH ROW
BEGIN
  p(:NEW.rid);
END aut_t;
/
Session 1 Session 2
UPDATE /*+ RETRY_ON_ROW_CHANGE */ t
SET col = col * -1;		  
  SELECT * FROM logtable;

UPDATE t
SET col = 888888
WHERE col1 = 40000;

COMMIT;

SELECT * FROM logtable;
So the row with id 40000 is updated and committed, while our statement from session 1 has not reached this row yet.
After session 1 completed the update it says: 50000 rows updated. But here is what happened (from session 1):
SELECT *
FROM t
WHERE col1 BETWEEN 39999 AND 40001;

ID         COL
---------- ----------
39999      - 39999
40000      -888888
40001      - 40001		 SELECT * FROM logtable;

LAST_ID    NUMBER_OF_PROCESSED_IDS
---------- -----------------------
50000      89922
Session 2 has set COL2 to 888888, and session 1 has set this value to -888888.

By the number_of_processed_ids column you can deduce that the statement was restarted once it has fetched the block with ID 40000 in it. I also checked it in session 2 by reexecuting the last statement several times. The last_id column dropped back to 1 and the entire statement was re-executed. This leads to a total of 89,922 (50,000 + almost 40,000) number of processed ID's. So the statement was retried on detecting a row change. And the table doesn't need the ROWDEPENDENCIES clause to make this happen.

But what's the point? Oracle has its write consistency mechanism that makes sure a statement gets restarted under the covers if during DML a row has been changed that has not been locked yet. For example, if we add a where clause to the update statement saying "id = col", then the statement sets out to update 50,000 rows. Now, if another session update the col value of ID 40000 to 888888, the update statement of session 1 will detect that automatically and restarts the statement. Without the need for a RETRY_ON_ROW_CHANGE hint.

Now from session 1 with the same DDL as above:
UPDATE t
SET col = col * -1
WHERE pid = col;		  
  SELECT * FROM logtable;

UPDATE t
SET col = 888888
WHERE pid = 40000;

COMMIT;

SELECT * FROM logtable;
COMMIT;

SELECT *
FROM t
WHERE pid BETWEEN 39999 AND 40001;

 ID         COL
---------- ----------
39999       -39999
40000       888888
40001       -40001

3 rows selected.

SELECT * FROM logtable;		  
So here Oracle's write consistency mechanism made the statement rollback and re-execute. Which, by the way, is a good reason to not use autonomous transactions in database triggers: they might execute twice for a single row.

If someone can enlighten me with a use case for the RETRY_ON_ROW_CHANGE hint, I would be happy.

-- everything above is from Rob. What follows is some of the comments in the blog following his demo.

In the same category, REWRITE_OR_ERROR appeared in 10g
https://download.oracle.com/docs/cd/B19306_01/server.102/b14223/qradv.htm#BABIAIHJ
 ~ Laurent Schneider

Back to the original question - a user case for RETRY_..., I'm guessing it will be related to editioning, seeing as the other two are also mentioned heavily in that context.

I'm thinking something like a cross-edition trigger may need to get a "fuller" restart by using the hint rather than the default offered by standard write consistency.
 ~ Connnor McDonald

My guess - the standard write consistency mechanism triggers a restart if the columns referenced in the statement (or triggers)change, the RETRY_ON_ROW_CHANGE, being based on the ora_rowscn, restarts if ANY column has been modified.
 ~ Alberto Dell'Era

I'd bet the IGNORE_ROW_ON_DUPKEY_INDEX hint was added because (1) 10gR2's DML Error Logging doesn't log unique key violations and (2) the venerable EXCEPTIONS INTO clause allows the violations in. So the hint is allows Oracle to "take the first one" and reject the rest https://tkyte.blogspot.com/2005/04/understanding.html
 ~ Duke
 
Semijoin Hints
The first semijoin hint, NL_SJ, was introduced in 8.0.0, HASH_SJ and MERGE_SJ in 8.1.0, and NO_SEMI_JOIN and SEMIJOIN in 9.0.0.
CUBE_SJ Cube Semi-Join
TBD
HASH_SJ Hash Semi-Join
TBD
MERGE_SJ Transforms a correlated EXISTS subquery into a merge semi-join to access the specified table.
/*+ MERGE_SJ (<tablespec>) */
TBD
NL_SJ Nested Loop Semi-Join
TBD
NO_SEMIJOIN Undocumented
TBD
NO_SEMI_TO_INNER Undocumented
TBD
SEMIJOIN Undocumented
TBD
SEMIJOIN_DRIVER Undocumented
TBD
SEMI_TO_INNER Undocumented
TBD
USE_SEMI Undocumented
TBD
 
Stats Hints
All three of these hints were introduced in 10.1.0.3.
ANSWER_QUERY_USING_STATS and
NO_ANSWER_QUERY_USING_STATS		 Undocumented
TBD
COLUMN_STATS Undocumented
TBD
DBMS_STATS Undocumented: According to comments from Jonathan Lewis this not an Oracle hint but rather explanatory text put in by some developers to label code and make it easier to identify the statement as being generated by DBMS_STATS.
/*+ DBMS_STATS */
TBD
GATHER_OPTIMIZER_STATISTICS

Documented		 Instructs the optimizer to enable statistics gathering during the following types of bulk load
  • CREATE TABLE ... AS SELECT
  • INSERT INTO ... SELECT into an empty table using a direct-path insert
/*+ GATHER_OPTIMIZER_STATISTICS */
SELECT */+ gather_optimizer_statistics */ *
FROM servers s, serv_inst i
WHERE s.server_id = i.server_id;
GATHER_PLAN_STATISTICS Must be the first hint if more than one hint is present. - STATS?
TBD
INDEX_STATS Undocumented
TBD
NO_GATHER_OPTIMIZER_STATISTICS

Documented		 Instructs the optimizer to enable statistics gathering during the following types of bulk load
  • CREATE TABLE ... AS SELECT
  • INSERT INTO ... SELECT into an empty table using a direct-path insert
/*+ NO_GATHER_OPTIMIZER_STATISTICS */
SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ *
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
NO_STATS_GSETS Undocumented - STATS?
TBD
SYSTEM_STATS Undocumented
TBD
TABLE_STATS Undocumented
TBD
 
Vector Hints
NO_USE_VECTOR Undocumented
TBD
NO_VECTOR_TRANSFORM Undocumented
TBD
NO_VECTOR_TRANSFORM_DIMS Undocumented
TBD
NO_VECTOR_TRANSFORM_FACT Undocumented
TBD
USE_VECTOR_AGGREGATION Undocumented
TBD
VECTOR_READ Undocumented
TBD
VECTOR_READ_TRACE Undocumented
TBD
VECTOR_TRANSFORM Undocumented
TBD
VECTOR_TRANSFORM_DIMS Undocumented
TBD
VECTOR_TRANSFORM_FACT Undocumented
TBD
 
XML and XMLINDEX_REWRITE Hints
COST_XML_QUERY_REWRITE Undocumented
TBD
FORCE_XML_QUERY_REWRITE Undocumented
TBD
INLINE_XMLTYPE_NT Undocumented
TBD
NO_COST_XML_QUERY_REWRITE Undocumented
TBD
NO_XML_DML_REWRITE Undocumented
TBD
NO_XML_QUERY_REWRITE

Documented		 Instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements.
/*+ NO_XML_QUERY_REWRITE */
SELECT /*+NO_XML_QUERY_REWRITE*/ XMLQUERY('<A/>')
FROM dual;
NO_XMLINDEX_REWRITE

Documented		 Instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements.
/*+ NO_XMLINDEX_REWRITE */
SELECT /*+ NO_XMLINDEX_REWRITE */ COUNT(*)
FROM t
WHERE existsNode(OBJECT_VALUE, '/*') = 1;
NO_XMLINDEX_REWRITE_IN_SELECT Undocumented
TBD
XML_DML_RWT_STMT Undocumented
TBD
XMLINDEX_REWRITE Undocumented
TBD
XMLINDEX_REWRITE_IN_SELECT Undocumented
TBD
XMLINDEX_SEL_IDX_TBL Undocumented
TBD
XMLTSET_DML_ENABLE Undocumented
TBD

Related Topics
Histograms
Index Scans
Outlines
SELECT Statements
Tuning
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx