Oracle SQL Hints --- Run Time And Data Maniplulation Hints

Date: 2013-01-08 05:08:05

Share to

Run Time Hints

APPEND

Usage: APPEND

Description: Instructs SQL executor to insert data in appending mode.

HELLODBA.COM>exec sql_explain('insert /*+append*/ into t_objects_bak select * from t_objects', 'BASIC OUTLINE')

--------------------------------------------

| Id | Operation          | Name          |

--------------------------------------------

|   0 | INSERT STATEMENT   |               |

|   1 | LOAD AS SELECT    | T_OBJECTS_BAK |

|   2 |   TABLE ACCESS FULL| T_OBJECTS     |

--------------------------------------------

APPEND_VALUES

Usage: APPEND_VALUES

Description: Instructs SQL executor to insert data in appending mode. It just works in the INSERT … VALUES statement.

HELLODBA.COM>exec sql_explain('insert /*+append_values*/ into t_objects(object_id, owner, object_name, status) values(:1, :2, :3, :4)', 'BASIC OUTLINE')

--------------------------------------

| Id | Operation        | Name      |

--------------------------------------

|   0 | INSERT STATEMENT |           |

|   1 | LOAD AS SELECT | T_OBJECTS |

|   2 |   BULK BINDS GET |           |

--------------------------------------

NOAPPEND

Usage: NOAPPEND

Description: Instructs SQL executor to insert data in appending mode.

HELLODBA.COM>exec sql_explain('insert /*+noappend*/ into t_objects_bak select * from t_objects', 'BASIC OUTLINE')

--------------------------------------------------

| Id | Operation                | Name          |

--------------------------------------------------

|   0 | INSERT STATEMENT         |               |

|   1 | LOAD TABLE CONVENTIONAL | T_OBJECTS_BAK |

|   2 |   TABLE ACCESS FULL      | T_OBJECTS     |

--------------------------------------------------

NLJ_BATCHING

Usage: NLJ_BATCHING

Description: Instructs the optimizer to use nested-loop join to access table in batch.

HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NLJ_BATCHING(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');

---------------------------------------------------------------------------------------------------

| Id | Operation                      | Name             | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 |

|   1 | SORT AGGREGATE                |                  |     1 |    68 |            |          |

|   2 |   NESTED LOOPS                 |                  |       |       |            |          |

|   3 |    NESTED LOOPS                |                  | 7985 |   530K|   914   (1)| 00:00:10 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 |

|* 5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 |

|* 6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 |

|   7 |    TABLE ACCESS BY INDEX ROWID | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

NO_NLJ_BATCHING

Usage: NO_NLJ_BATCHING([<@Block>] <Table>)

Description: Prevents the optimizer to use nested-loop join to access table in batch.

HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NO_NLJ_BATCHING(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');

---------------------------------------------------------------------------------------------------

| Id | Operation                      | Name             | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 |

|   1 | SORT AGGREGATE                |                  |     1 |    68 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 |

|   3 |    NESTED LOOPS                |                  | 7985 |   530K|   914   (1)| 00:00:10 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 |

|* 5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 |

|* 6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

NLJ_PREFETCH

Usage: NLJ_PREFETCH([<@Block>] <Table>)

Description: Instructs the optimizer to use nested-loop join to pre-fetch the table.

HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NLJ_PREFETCH(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');

---------------------------------------------------------------------------------------------------

| Id | Operation                      | Name             | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 |

|   1 | SORT AGGREGATE                |                  |     1 |    68 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 |

|   3 |    NESTED LOOPS                |                  | 7985 |   530K|   914   (1)| 00:00:10 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 |

|* 5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 |

|* 6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |    2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

NO_NLJ_PREFETCH

Usage: NO_NLJ_PREFETCH([<@Block>] <Table>)

Description: Prevents the optimizer to use nested-loop join to pre-fetch the table.

HELLODBA.COM>exec sql_explain('select /*+ use_nl(o t) index(o T_OBJECTS_M_IDX8) index(t T_TABLES_IDX3) LEADING(t) NO_NLJ_PREFETCH(o)*/count(LIO) from T_OBJECTS_M O, T_TABLES t where o.owner=t.owner and o.object_name=t.table_name and t.tablespace_name=:A', 'TYPICAL OUTLINE');

---------------------------------------------------------------------------------------------------

| Id | Operation                      | Name             | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                  |     1 |    68 |   914   (1)| 00:00:10 |

|   1 | SORT AGGREGATE                |                  |     1 |    68 |            |          |

|   2 |   NESTED LOOPS                 |                  |       |       |            |          |

|   3 |    NESTED LOOPS                |                  | 7985 |   530K|   914   (1)| 00:00:10 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_TABLES         |   299 | 10465 |    16   (0)| 00:00:01 |

|* 5 |      INDEX RANGE SCAN          | T_TABLES_IDX3    |   299 |       |     1   (0)| 00:00:01 |

|* 6 |     INDEX RANGE SCAN           | T_OBJECTS_M_IDX8 |     1 |       |     2   (0)| 00:00:01 |

|   7 |    TABLE ACCESS BY INDEX ROWID | T_OBJECTS_M      |    27 |   891 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

CACHE

Usage: CACHE([<@Block>] <Table>)

Description: Instructs Oracle to put the read data block to the end of MRU in LRU chain.

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';

NAME                                VALUE

------------------------------ ----------

table scans (short tables)             37

table scans (long tables)               0

HELLODBA.COM>select /*+full(t) cache(t)*/count(*) from t_objects t;

… …

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';

NAME                                VALUE

------------------------------ ----------

table scans (short tables)             37

table scans (long tables)               1

NOCACHE

Usage: NOCACHE([<@Block>] <Table>)

Description: Instructs Oracle to put the read data block to the end of LRU in LRU chain.

HELLODBA.COM>alter system flush buffer_cache;

System altered.

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';

NAME                                VALUE

------------------------------ ----------

table scans (short tables)             35

table scans (long tables)              1

HELLODBA.COM>select /*+full(t) nocache(t)*/count(*) from t_tables t;

… …

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic# = n.statistic# and name like 'table scans% tables)';

NAME                                VALUE

------------------------------ ----------

table scans (short tables)             36

table scans (long tables)               1

CACHE_TEMP_TABLE

Usage: CACHE_TEMP_TABLE([<@Block>] <Table>)

Description: Instructs Oracle to put the read temp block to the end of MRU in LRU chain. This hint指normally be used in internal statement.

HELLODBA.COM>begin

2     sql_explain('WITH

3                     A AS (SELECT /*+qb_name(AV)*/''x'' M FROM DUAL),

4                     B AS (SELECT /*+qb_name(BV)*/DISTINCT LEVEL L FROM A CONNECT BY LEVEL <= 10),

5                     C AS (SELECT /*+qb_name(CV)*/LPAD(LEVEL-1,(SELECT COUNT(*) FROM B),''0'') N FROM DUAL CONNECT BY LEVEL <= POWER(10,(SELECT COUNT(*) FROM B)))

6                     SELECT /*+qb_name(M)*/* FROM A, B, C where rownum>=1', 'BASIC PREDICATE');

7 end;

8 /

… …

Predicate Information (identified by operation id):

---------------------------------------------------

   7 - filter(LEVEL<=10)

10 - filter(ROWNUM>=1)

18 - filter(LEVEL<=POWER(10, (SELECT /*+ */ COUNT(*) FROM (SELECT

              /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "L" FROM

              "SYS"."SYS_TEMP_0FD9D663A_F1E95AE7" "T1") "B")))

NO_LOAD

Usage: NO_LOAD

Description: Not allow the data to be loaded directly.

HELLODBA.COM>alter session enable parallel dml;

Session altered.

HELLODBA.COM>alter session set "_disable_parallel_conventional_load"=true;

Session altered.

HELLODBA.COM>exec sql_explain('insert /*+ no_load(d) parallel(d 2) */ into t_objects_dummy2 d select /*+parallel(o 2)*/* from t_objects o', 'BASIC');

-----------------------------------------------------

| Id | Operation                | Name             |

-----------------------------------------------------

|   0 | INSERT STATEMENT         |                  |

|   1 | LOAD TABLE CONVENTIONAL | T_OBJECTS_DUMMY2 |

|   2 |   PX COORDINATOR         |                  |

|   3 |    PX SEND QC (RANDOM)   | :TQ10000         |

|   4 |     PX BLOCK ITERATOR    |                  |

|   5 |      TABLE ACCESS FULL   | T_OBJECTS        |

-----------------------------------------------------

NO_SUBSTRB_PAD

Usage: NO_SUBSTRB_PAD

Description: No extra byte be padded when perform SUBSTRB on multiple-bytes character string.

HELLODBA.COM>select /*+ */substrb(chistr,1,2) str, lengthb(substrb(chistr,1,2)) len from (select '多字节字符串' chistrfrom dual ) v;

STR         LEN

---- ----------

              2

HELLODBA.COM>select /*+ NO_SUBSTRB_PAD */substrb(chistr,1,2) str, lengthb(substrb(chistr,1,2)) len from (select '多字节字符串' chistr from dual ) v;

STR        LEN

--- ----------

RESULT_CACHE

Usage: RESULT_CACHE

Description: Instructs Oracle cache the result to be used by following query.

HELLODBA.COM>show parameter result_cache

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

client_result_cache_lag              big integer 3000

client_result_cache_size             big integer 0

result_cache_max_result              integer     5

result_cache_max_size                big integer 1M

result_cache_mode                    string      MANUAL

result_cache_remote_expiration       integer     0

HELLODBA.COM>exec sql_explain('select * from t_tables t, (select /*+ result_cache */* from t_objects o where object_type = :A) v where v.owner = t.owner and v.object_name = t.table_name', 'BASIC PREDICATE');

---------------------------------------------------------------------

| Id | Operation                      | Name                       |

---------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                            |

|* 1 | HASH JOIN                     |                            |

|   2 |   VIEW                         |                            |

|   3 |    RESULT CACHE                | b6zzbhhgh4knw21npw13q564wn |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_OBJECTS                  |

|* 5 |      INDEX RANGE SCAN          | T_OBJECTS_IDX7             |

|   6 |   TABLE ACCESS FULL           | T_TABLES                   |

---------------------------------------------------------------------

Result Cache Information (identified by operation id):

------------------------------------------------------

   3 - column-count=16; dependencies=(DEMO.T_OBJECTS); attributes=(ordered); parameters=(nls, :A); name="select /*+ result_cache */* from t_objects o where object_type = :A"

NO_RESULT_CACHE

Usage: NO_RESULT_CACHE

Description: Prevents Oracle cache the result to be used by following query.

HELLODBA.COM>alter session set result_cache_mode=force;

Session altered.

HELLODBA.COM>exec sql_explain('select /*+ no_result_cache */* from t_tables t', 'TYPICAL');

------------------------------------------------------------------------------

| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |          | 2696 |   634K|    28   (4)| 00:00:01 |

|   1 | TABLE ACCESS FULL| T_TABLES | 2696 |   634K|    28   (4)| 00:00:01 |

------------------------------------------------------------------------------

SYS_DL_CURSOR

Usage: SYS_DL_CURSOR

Description: This hint is added in the internal statement generated by SQL*Loader when performing direct loading (Direct=TRUE).

Demo (Below statement is captured from library cache after run SQL*Loader to load data directly):

HELLODBA.COM>select sql_text, module from v$sql where sql_text like 'INSERT /*+ SYS_DL_CURSOR */%';

SQL_TEXT                                                                                         MODULE

------------------------------------------------------------------------------------------------ ------

INSERT /*+ SYS_DL_CURSOR */ INTO "DEMO"."T_TABLES_LD" ("OWNER","TABLE_NAME") VALUES (NULL,NULL) SQL Loader Direct Path Load

TRACING

Usage: TRACING(verify <Number> strip <Number>)

Description: Unknown. It might be used to trace the ASM stripping. This hint will add an extra operation, MONITORING STRIP, in the execution plan.

HELLODBA.COM>exec sql_explain('select /*+qb_name(M) full(t_objects) TRACING(verify 1, strip 1) */* from t_objects', 'TYPICAL OUTLINE');

--------------------------------------------------------------------------------

| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           | 72116 | 7042K|   297   (3)| 00:00:03 |

|   1 | MONITORING STRIP |           | 72116 | 7042K|   297   (3)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T_OBJECTS | 72116 | 7042K|   297   (3)| 00:00:03 |

--------------------------------------------------------------------------------

Data Manipulation Hints

CHANGE_DUPKEY_ERROR_INDEX

Usage: CHANGE_DUPKEY_ERROR_INDEX(<Table>, <Index>) or CHANGE_DUPKEY_ERROR_INDEX(<Table>, <Indexed Columns>)

Description: When unique constraint is violated, it will raise ORA-38911 instead of ORA-00001.

HELLODBA.COM>insert into t_tables(owner, table_name) values ('SYS', 'TAB$');

insert into t_tables(owner, table_name) values ('SYS', 'TAB$')

*

ERROR at line 1:

ORA-00001: unique constraint (DEMO.T_TABLES_PK) violated

HELLODBA.COM>insert /*+CHANGE_DUPKEY_ERROR_INDEX(t_tables t_tables_pk)*/into t_tables(owner, table_name) values ('SYS','TAB$');

insert /*+CHANGE_DUPKEY_ERROR_INDEX(t_tables t_tables_pk)*/into t_tables(owner, table_name) values ('SYS', 'TAB$')

*

ERROR at line 1:

ORA-38911: unique constraint (DEMO.T_TABLES_PK) violated

IGNORE_ROW_ON_DUPKEY_INDEX

Usage: IGNORE_ROW_ON_DUPKEY_INDEX(<Table> <Unique Index>) or IGNORE_ROW_ON_DUPKEY_INDEX(<Table> <Unique INdex columns>)

Description: The duplicated data will be ignored when insert data to a table with unique constraint.

HELLODBA.COM>create table t(a number primary key);

Table created.

HELLODBA.COM>insert into t values(2);

1 row created.

HELLODBA.COM>commit;

Commit complete.

HELLODBA.COM>insert /*+IGNORE_ROW_ON_DUPKEY_INDEX(t (a))*/into t select level from dual connect by level <= 3;

2 rows created.

HELLODBA.COM>commit;

Commit complete.

HELLODBA.COM>select * from t;

         A

----------

         1

         2

         3

RETRY_ON_ROW_CHANGE

Usage: RETRY_ON_ROW_CHANGE

Description: When the transaction of UPDATE or DELETE is committing, if the cached data changed, the transaction will be rebounded.

HELLODBA.COM>-- 会话 1

HELLODBA.COM>create table t(a number primary key);

Table created.

HELLODBA.COM>insert into t values(1);

1 row created.

HELLODBA.COM>insert into t values(2);

1 row created.

HELLODBA.COM>commit;

Commit complete.

HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T');

PL/SQL procedure successfully completed.

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

consistent gets                                                        1684

no work - consistent read gets                                          549

transaction rollbacks                                                     0

HELLODBA.COM>update /*+RETRY_ON_ROW_CHANGE*/t set a=a+10 where a>=2;

1 rows updated.

HELLODBA.COM>-- 会话 2

HELLODBA.COM>update t set a=a+100 where a=1;

1 row updated.

HELLODBA.COM>commit;

Commit complete.

HELLODBA.COM>-- 会话 1

HELLODBA.COM>commit;

Commit complete.

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

consistent gets                                                        1686

no work - consistent read gets                                          550

transaction rollbacks                                                     0

Without the hint, we get below result.

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

consistent gets                                                        1708

no work - consistent read gets                                          555

transaction rollbacks                                                     4

... ...

HELLODBA.COM>select name, value from v$mystat m, v$statname n where m.statistic#=n.statistic# and name in ('consistent gets', 'no work - consistent read gets', 'transaction rollbacks');

NAME                                                                 VALUE

---------------------------------------------------------------- ----------

consistent gets                                                        1709

no work - consistent read gets                                          555

transaction rollbacks                                                     4

Previous：Oracle SQL Hints --- Statistics Data And Optimizer Hints

Next：Oracle SQL Hints --- Hierarchy Query And XML Query Hints

Return To this Category