Oracle SQL提示含义与示例 --- 关联提示

日期： 2013-01-08 04:09:09

分享到

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

关联提示

NL_AJ

语法：NL_AJ([<子查询块>])

描述：指示优化器将主查询中的表与子查询中的表做嵌套循环反关联（Nested Loop Anti-Join）操作；如果提示出现在子查询中，则不需要参数指定查询块标识；

HELLODBA.COM>exec sql_explain('select /*+nl_aj(@inv)*/* from t_tables t where not exists (select /*+qb_name(inv)*/1 from t_users u where t.owner=u.username)', 'BASIC OUTLINE')

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

| Id | Operation          | Name       |

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

|   0 | SELECT STATEMENT   |            |

|   1 | NESTED LOOPS ANTI |            |

|   2 |   TABLE ACCESS FULL| T_TABLES   |

|   3 |   INDEX UNIQUE SCAN| T_USERS_UK |

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

HASH_AJ

语法：HASH_AJ([<子查询块>])

描述：指示优化器将主查询中的表与子查询中的表做哈希反关联（Hash Anti-Join）操作；如果提示出现在子查询中，则不需要参数指定查询块标识；

HELLODBA.COM>exec sql_explain('select /*+leading(t) hash_aj(@inv)*/* from t_tables t where not exists (select /*+qb_name(inv)*/1 from t_users u where t.owner=u.username)', 'BASIC OUTLINE')

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

| Id | Operation          | Name       |

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

|   0 | SELECT STATEMENT   |            |

|   1 | HASH JOIN ANTI    |            |

|   2 |   TABLE ACCESS FULL| T_TABLES   |

|   3 |   INDEX FULL SCAN | T_USERS_UK |

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

MERGE_AJ

语法：MERGE_AJ([<子查询块>])

描述：指示优化器将主查询中的表与子查询中的表做合并反关联（Merge Anti-Join）操作；如果提示出现在子查询中，则不需要参数指定查询块标识；

HELLODBA.COM>exec sql_explain('select /*+merge_aj(@inv)*/* from t_tables t where not exists (select /*+qb_name(inv)*/1from t_users u where t.owner=u.username)', 'BASIC OUTLINE')

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

| Id | Operation                    | Name          |

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

|   0 | SELECT STATEMENT             |               |

|   1 | MERGE JOIN ANTI             |               |

|   2 |   TABLE ACCESS BY INDEX ROWID| T_TABLES      |

|   3 |    INDEX FULL SCAN           | T_TABLES_IDX1 |

|   4 |   SORT UNIQUE                |               |

|   5 |    INDEX FULL SCAN           | T_USERS_UK    |

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

USE_HASH

语法：USE_HASH([<@查询块>] <表1> [<表2>])

描述：指示优化器采用哈希关联。如果参数中仅指定一张表，则需要用LEDING提示来指定关联顺序。

HELLODBA.COM>exec sql_explain('select /*+ use_hash(o) leading(t) */* from t_objects o, t_tables t where o.owner=t.owner and o.object_name=t.table_name and o.object_id = :A', 'TYPICAL OUTLINE');

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

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

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

|   0 | SELECT STATEMENT             |              |     1 |   326 |   144   (1)| 00:00:01 |

|* 1 | HASH JOIN                   |              |     1 |   326 |   144   (1)| 00:00:01 |

|   2 |   TABLE ACCESS FULL          | T_TABLES     | 2071 |   412K|   142   (1)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS    |     1 |   122 |     2   (0)| 00:00:01 |

|* 4 |    INDEX UNIQUE SCAN         | T_OBJECTS_PK |     1 |       |     1   (0)| 00:00:01 |

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

NO_USE_HASH

语法：NO_USE_HASH([<@查询块>] <表1> [<表2> ...])

描述：指示优化器不要使用参数中指定的表作为哈希关联的内表；如果所有表都被指定，则不会使用哈希关联。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) no_use_hash(t) */* from t_objects o, t_tables t where o.owner=t.owner and o.object_name=t.table_name', 'TYPICAL OUTLINE');

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

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

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

|   0 | SELECT STATEMENT   |           | 47585 |    14M| 1813   (1)| 00:00:08 |

|* 1 | HASH JOIN         |           | 47585 |    14M| 1813   (1)| 00:00:08 |

|   2 |   TABLE ACCESS FULL| T_TABLES | 2071 |   412K|   142   (1)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T_OBJECTS | 47585 | 5669K| 1670   (1)| 00:00:07 |

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

USE_MERGE

语法：USE_MERGE([<@查询块>] <表1> [<表2>])

描述：指示优化器采用合并关联。如果参数中仅指定一张表（内表），则需要用LEDING提示来指定关联顺序。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) use_merge(t o) */* from t_objects o, t_tables t where o.owner=t.owner and o.object_name=t.table_name', 'TYPICAL OUTLINE');

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

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

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

|   0 | SELECT STATEMENT    |           | 47585 |    14M|       | 6360   (1)| 00:00:26 |

|   1 | MERGE JOIN         |           | 47585 |    14M|       | 6360   (1)| 00:00:26 |

|   2 |   SORT JOIN         |           | 2071 |   412K| 1288K|   447   (1)| 00:00:02 |

|   3 |    TABLE ACCESS FULL| T_TABLES | 2071 |   412K|       |   142   (1)| 00:00:01 |

|* 4 |   SORT JOIN         |           | 47585 | 5669K|    14M| 5913   (1)| 00:00:24 |

|   5 |    TABLE ACCESS FULL| T_OBJECTS | 47585 | 5669K|       | 1670   (1)| 00:00:07 |

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

NO_USE_MERGE

语法：NO_USE_MERGE([<@查询块>] <表1> [<表2> ...])

描述：指示优化器不要使用参数中指定的表作为合并关联的内表；如果所有表都被指定，则不会使用合并关联。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) no_use_merge(t o) */o.object_id, t.table_name from t_objects o, t_tables t where o.owner=t.owner and o.object_name=t.table_name and o.object_id < :A', 'TYPICAL OUTLINE');

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

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

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

|   0 | SELECT STATEMENT             |              | 2379 |   137K|    11 (10)| 00:00:01 |

|   1 | NESTED LOOPS                |              | 2379 |   137K|    11 (10)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS    | 2379 | 83265 |    10   (0)| 00:00:01 |

|* 3 |    INDEX RANGE SCAN          | T_OBJECTS_PK |   428 |       |     2   (0)| 00:00:01 |

|* 4 |   INDEX UNIQUE SCAN          | T_TABLES_PK |     1 |    24 |     0   (0)| 00:00:01 |

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

USE_NL

语法：USE_NL([<@查询块>] <表1> [<表2>])

描述：指示优化器采用嵌套循环关联，并使用指定表为内表。如果参数中仅指定一张表（内表），则需要用LEDING提示来指定关联顺序。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) use_nl(t o) */* from t_objects o, t_tables t where o.owner=t.ownerand o.object_name=t.table_name', 'TYPICAL OUTLINE');

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

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

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

|   0 | SELECT STATEMENT             |             | 47585 |    14M| 4830   (1)| 00:00:20 |

|   1 | NESTED LOOPS                |             | 47585 |&nbs