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

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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 |