Oracle SQL提示含义与示例 --- 语句运行和数据操作提示

日期： 2013-01-08 05:08:05

分享到

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。

语句运行提示

APPEND

语法：APPEND

描述：指示优化器以追加方式向表直接插入数据；

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

语法：APPEND_VALUES

描述：指示优化器以追加方式向表直接插入数据；该提示仅支持INSERT ... VALUES 形式的语句；

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

描述：禁止优化器以追加方式向表直接插入数据；

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

语法：NLJ_BATCHING

描述：指示优化器以嵌套循环关联批量读取的方式访问表

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

语法：NO_NLJ_BATCHING([<@查询块>] <表>)

描述：禁止优化器以嵌套循环关联批量读取的方式访问表

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

语法：NLJ_PREFETCH([<@查询块>] <表>)

描述：指示优化器以嵌套循环关联预提取的方式访问表

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

语法：NO_NLJ_PREFETCH([<@查询块>] <表>)

描述：禁止优化器以嵌套循环关联预提取的方式访问表

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

语法：CACHE([<@查询块>] <表>)