HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

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

[English]

作者: fuyuncat

来源: www.HelloDBA.com

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

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

《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 |    14M|  4830   (1)| 00:00:20 |

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

|   3 |   TABLE ACCESS BY INDEX ROWID| T_TABLES    |     1 |   204 |     1   (0)| 00:00:01 |

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

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

USE_NL_WITH_INDEX

语法:USE_NL_WITH_INDEX([<@查询块>] <> [索引1 ...]) 或者 USE_NL_WITH_INDEX([<@查询块>] <> [(<索引字段列表1>) ...])

描述:指示优化器采用嵌套循环关联,并使用指定表为内表,且对指定表的访问要通过索引访问。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) use_nl_with_index(o (status owner object_name)) leading(t) */o.object_name, 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 |    10M|   666K  (1)| 00:44:28 |

|   1 |  NESTED LOOPS      |                | 47585 |    10M|   666K  (1)| 00:44:28 |

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

|*  3 |   INDEX FULL SCAN  | T_OBJECTS_IDX1 |    23 |   690 |   322   (1)| 00:00:02 |

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

NO_USE_NL

语法:NO_USE_NL([<@查询块>] <1> [<2> ...])

描述:指示优化器不要使用参数中指定的表作为嵌套循环关联的内表;如果所有表都被指定,则不会使用嵌套循环关联。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) no_use_nl(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             |              |     1 |    59 |    14   (8)| 00:00:01 |

|   1 |  MERGE JOIN                  |              |     1 |    59 |    14   (8)| 00:00:01 |

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

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

|*  4 |   FILTER                     |              |       |       |            |          |

|   5 |    INDEX FULL SCAN           | T_TABLES_PK  |  2071 | 49704 |    11   (0)| 00:00:01 |

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

USE_MERGE_CARTESIAN

语法:USE_MERGE_CARTESIANUSE_SEMI([<@查询块>] <1> [<2>])

描述:指示优化器采用笛卡尔合并关联。

示例(11.2.0.1):

HELLODBA.COM>exec sql_explain('select /*+use_nl(ts) USE_MERGE_CARTESIAN(d) leading(u)*/count(*) from t_users u, t_datafiles d, t_tablespaces ts where ts.tablespace_name = d.tablespace_name and u.default_tablespace = ts.tablespace_name', 'TYPICAL OUTLINE');

 

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

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

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

|   0 | SELECT STATEMENT      |                 |     1 |    32 |     8  (25)| 00:00:01 |

|   1 |  SORT AGGREGATE       |                 |     1 |    32 |            |          |

|   2 |   MERGE JOIN CARTESIAN|                 |    14 |   448 |     8  (25)| 00:00:01 |

|   3 |    NESTED LOOPS       |                 |    31 |   465 |     3   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL | T_USERS         |    31 |   217 |     3   (0)| 00:00:01 |

|*  5 |     INDEX UNIQUE SCAN | T_TABLESPACE_PK |     1 |     8 |     0   (0)| 00:00:01 |

|   6 |    BUFFER SORT        |                 |     1 |    17 |     8  (25)| 00:00:01 |

|*  7 |     TABLE ACCESS FULL | T_DATAFILES     |     1 |    17 |     3   (0)| 00:00:01 |

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

LEADING

语法:LEADING([<@查询块>] <1> [<2> ... ])

描述:指示优化器采用特定顺序关联表

USE_HASH示例。

USE_ANTI

语法:USE_ANTII([<@查询块>] <1> [<2>])

描述:指示优化器采用反关联。仅在反关联并行查询的递归调用语句上观察到。

示例(9.2.0.5):

HELLODBA.COM>select /*+ parallel(o 2) */count(*) from t_objects o where owner not in (select /*+ */username from t_users u);

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=47 Card=1 Bytes=16)

   1    0   SORT (AGGREGATE)

   2    1     SORT* (AGGREGATE)                                                         :Q17863001

   3    2       HASH JOIN* (ANTI) (Cost=47 Card=1 Bytes=16)                             :Q17863001

   4    3         TABLE ACCESS* (FULL) OF 'T_OBJECTS' (Cost=22 Card=32435 Bytes=227045) :Q17863001

   5    3         TABLE ACCESS* (FULL) OF 'T_USERS' (Cost=2 Card=46 By tes=414)         :Q17863000

   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F

                                   ROM (SELECT /*+ ORDERED NO_EXPAND USE_HASH(A

                                   3) USE_ANTI(A3) */ 0 FROM (SELECT /*+ NO_EXP

                                   AND ROWID(A4) */ A4."OWNER" C0 FROM "T_OBJEC

                                   TS" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A4)

                                    A2,:Q17863000 A3 WHERE A2.C0=A3.C0) A1

   3 PARALLEL_COMBINED_WITH_PARENT

   4 PARALLEL_COMBINED_WITH_PARENT

   5 PARALLEL_FROM_SERIAL

USE_SEMI

语法:USE_SEMI([<@查询块>] <1> [<2>])

描述:指示优化器采用半关联。仅在半关联并行查询的递归调用语句上观察到。

示例(9.2.0.5):

HELLODBA.COM>select /*+ parallel(o default) */* from t_objects o where subobject_name in (select /*+parallel(t default)*/table_name from t_tables t where o.owner=t.owner);

 

no rows selected

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=26 Bytes=3120)

   1    0   NESTED LOOPS* (SEMI) (Cost=8 Card=26 Bytes=3120)                  :Q17689000

   2    1     TABLE ACCESS* (FULL) OF 'T_OBJECTS' (Cost=6 Card=51 Bytes=4743) :Q17689000

             3    1     INDEX* (RANGE SCAN) OF 'T_TABLES_UK1' (NON-UNIQUE)    :Q17689000

   1 PARALLEL_TO_SERIAL            SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE

                                   X(A2 "T_TABLES_UK1") USE_SEMI(A2) */ A1.C0,A

                                   1.C1,A1.C2,A1.C3,A1.C4,A1.C5,A1.C6,A1.C7,A1.

                                   C8,A1.C9,A1.C10,A1.C11,A1.C12 FROM (SELECT /

                                   *+ NO_EXPAND ROWID(A3) */ A3."OWNER" C0,A3."

                                   OBJECT_NAME" C1,A3."SUBOBJECT_NAME" C2,A3."O

                                   BJECT_ID" C3,A3."DATA_OBJECT_ID" C4,A3."OBJE

                                   CT_TYPE" C5,A3."CREATED" C6,A3."LAST_DDL_TIM

                                   E" C7,A3."TIMESTAMP" C8,A3."STATUS" C9,A3."T

                                   EMPORARY" C10,A3."GENERATED" C11,A3."SECONDA

                                   RY" C12 FROM "T_OBJECTS" PX_GRANULE(0, BLOCK

                                   _RANGE, DYNAMIC)  A3 WHERE A3."SUBOBJECT_NAM

                                   E" IS NOT NULL) A1,"T_TABLES" A2 WHERE A1.C2

                                   =A2."TABLE_NAME" AND A1.C0=A2."OWNER"

NATIVE_FULL_OUTER_JOIN

语法:NATIVE_FULL_OUTER_JOIN([<@查询块>])

描述:指示优化器采用基于哈希关联的真正完全外关联操作。

HELLODBA.COM>exec sql_explain('select /*+NATIVE_FULL_OUTER_JOIN*/ts.tablespace_name, ts.block_size, u.user_id from t_tablespaces ts full outer join t_users u on ts.tablespace_name=u.default_tablespace and ts.max_extents<:A and u.user_id>:B', 'TYPICAL OUTLINE');

 

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

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

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

|   0 | SELECT STATEMENT      |               |    54 |  2322 |     5  (20)| 00:00:05 |

|   1 |  VIEW                 | VW_FOJ_0      |    54 |  2322 |     5  (20)| 00:00:05 |

|*  2 |   HASH JOIN FULL OUTER|               |    54 |  1350 |     5  (20)| 00:00:05 |

|   3 |    TABLE ACCESS FULL  | T_TABLESPACES |    15 |   240 |     2   (0)| 00:00:03 |

|   4 |    TABLE ACCESS FULL  | T_USERS       |    41 |   369 |     2   (0)| 00:00:03 |

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

NO_NATIVE_FULL_OUTER_JOIN

语法:NO_NATIVE_FULL_OUTER_JOIN([<@查询块>])

描述:禁止优化器采用的真正完全外关联操作。

HELLODBA.COM>exec sql_explain('select /*+NO_NATIVE_FULL_OUTER_JOIN*/ts.tablespace_name, ts.block_size, u.user_id from t_tablespaces ts full outer join t_users u on ts.tablespace_name=u.default_tablespace and ts.max_extents<:A and u.user_id>:B', 'TYPICAL OUTLINE');

 

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

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

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

|   0 | SELECT STATEMENT                 |                 |    58 |  2494 |    82   (0)| 00:00:01 |

|   1 |  VIEW                            |                 |    58 |  2494 |    82   (0)| 00:00:01 |

|   2 |   UNION-ALL                      |                 |       |       |            |          |

|   3 |    NESTED LOOPS OUTER            |                 |    15 |   435 |    41   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL            | T_TABLESPACES   |    15 |   240 |    11   (0)| 00:00:01 |

|   5 |     VIEW                         |                 |     1 |    13 |     2   (0)| 00:00:01 |

|*  6 |      FILTER                      |                 |       |       |            |          |

|*  7 |       TABLE ACCESS BY INDEX ROWID| T_USERS         |     1 |    11 |     2   (0)| 00:00:01 |

|*  8 |        INDEX RANGE SCAN          | T_USERS_PK      |     2 |       |     1   (0)| 00:00:01 |

|*  9 |    FILTER                        |                 |       |       |            |          |

|  10 |     TABLE ACCESS FULL            | T_USERS         |    43 |   473 |    19   (0)| 00:00:01 |

|* 11 |     FILTER                       |                 |       |       |            |          |

|* 12 |      TABLE ACCESS BY INDEX ROWID | T_TABLESPACES   |     1 |    13 |     1   (0)| 00:00:01 |

|* 13 |       INDEX UNIQUE SCAN          | T_TABLESPACE_PK |     1 |       |     0   (0)| 00:00:01 |

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

NO_CARTESIAN

语法:NO_CARTESIAN([查询块] <1> [<2> ...])

描述:禁止优化器产生对指定表的笛卡儿关联操作

HELLODBA.COM>exec sql_explain('select /*+ QB_NAME(M) FULL(D) NO_CARTESIAN(D) */t.owner, t.table_name, i.owner, i.index_name, d.* from t_datafiles d, t_constraints c, t_tables t, t_indexes i where t.tablespace_name=d.tablespace_name and c.owner=t.owner and c.table_name=t.table_name and c.r_owner = i.owner and c.r_constraint_name = i.index_name and d.file_id = :id', 'BASIC');

 

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

| Id  | Operation                      | Name               |

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

|   0 | SELECT STATEMENT               |                    |

|   1 |  NESTED LOOPS                  |                    |

|   2 |   HASH JOIN                    |                    |

|   3 |    NESTED LOOPS                |                    |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_CONSTRAINTS      |

|   5 |      INDEX FULL SCAN           | T_CONSTRAINTS_IDX4 |

|   6 |     TABLE ACCESS BY INDEX ROWID| T_TABLES           |

|   7 |      INDEX UNIQUE SCAN         | T_TABLES_PK        |

|   8 |    TABLE ACCESS FULL           | T_DATAFILES        |

|   9 |   INDEX UNIQUE SCAN            | T_INDEXES_PK       |

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

ORDERED

语法:ORDERED                     

描述:指示优化器采用表在FROM子句后出现的顺序进行关联;

HELLODBA.COM>exec sql_explain('SELECT /*+QB_NAME(M) ORDERED*/ * from t_objects o, t_users u where user_id=:A and u.username = o.owner','BASIC');

 

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

| Id  | Operation                    | Name       |

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

|   0 | SELECT STATEMENT             |            |

|   1 |  HASH JOIN                   |            |

|   2 |   TABLE ACCESS FULL          | T_OBJECTS  |

|   3 |   TABLE ACCESS BY INDEX ROWID| T_USERS    |

|   4 |    INDEX UNIQUE SCAN         | T_USERS_PK |

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

PX_JOIN_FILTER

语法:PX_JOIN_FILTER(<>)

描述:强制优化器采用位图过滤技术进行哈希外关联或者并行关联

HELLODBA.COM>exec sql_explain('SELECT /*+ qb_name(M) PX_JOIN_FILTER(t) */* FROM t_tables t,  t_datafiles d WHERE t.tablespace_name(+) = d.tablespace_name', 'TYPICAL OUTLINE');

 

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

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

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

|   0 | SELECT STATEMENT   |             |  1791 |   627K|    31   (4)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |             |  1791 |   627K|    31   (4)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T_DATAFILES |     6 |   708 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| T_TABLES    |  2388 |   562K|    28   (4)| 00:00:01 |

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

NO_PX_JOIN_FILTER

语法:NO_PX_JOIN_FILTER(<>)

描述:禁止优化器采用位图过滤技术进行哈希外关联或者并行关联

HELLODBA.COM>exec sql_explain('SELECT /*+ qb_name(M) NO_PX_JOIN_FILTER(t) */t.owner, d.file_id FROM t_tables t, t_datafiles d WHERE t.tablespace_name(+) = d.tablespace_name', 'TYPICAL OUTLINE');

 

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

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

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

|   0 | SELECT STATEMENT        |                  |  1791 | 59103 |    19   (6)| 00:00:01 |

|*  1 |  HASH JOIN OUTER        |                  |  1791 | 59103 |    19   (6)| 00:00:01 |

|   2 |   TABLE ACCESS FULL     | T_DATAFILES      |     6 |   114 |     3   (0)| 00:00:01 |

|   3 |   VIEW                  | index$_join$_001 |  2388 | 33432 |    16   (7)| 00:00:01 |

|*  4 |    HASH JOIN            |                  |       |       |            |          |

|*  5 |     INDEX FAST FULL SCAN| T_TABLES_IDX3    |  2388 | 33432 |     9   (0)| 00:00:01 |

|   6 |     INDEX FAST FULL SCAN| T_TABLES_IDX1    |  2388 | 33432 |    10   (0)| 00:00:01 |

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

NL_SJ

语法:NL_SJ([<子查询块>])

描述:指示优化器使用嵌套循环半关联.

HELLODBA.COM>exec sql_explain('select * from t_tables t where exists (select /*+nl_sj*/1 from t_objects o where t.owner=o.owner)', 'BASIC');

 

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

| Id  | Operation                | Name               |

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

|   0 | SELECT STATEMENT         |                    |

|   1 |  NESTED LOOPS SEMI       |                    |

|   2 |   TABLE ACCESS FULL      | T_TABLES           |

|   3 |   PARTITION HASH ITERATOR|                    |

|   4 |    INDEX RANGE SCAN      | T_OBJECTS_IDX_PART |

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

HASH_SJ

语法:HASH_SJ([<子查询块>])

描述:指示优化器使用哈希半关联.

HELLODBA.COM>exec sql_explain('select * from t_tables t where exists (select /*+hash_sj*/1 from t_users u where t.owner=u.username)', 'BASIC');

 

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

| Id  | Operation            | Name       |

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

|   0 | SELECT STATEMENT     |            |

|   1 |  HASH JOIN RIGHT SEMI|            |

|   2 |   INDEX FULL SCAN    | T_USERS_UK |

|   3 |   TABLE ACCESS FULL  | T_TABLES   |

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

MERGE_SJ

语法:MERGE_SJ([<子查询块>])

描述:指示优化器使用合并半关联.

HELLODBA.COM>exec sql_explain('select * from t_tables t where exists (select /*+merge_sj*/1 from t_objects o where t.owner=o.owner)', 'BASIC');

 

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

| Id  | Operation                      | Name           |

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

|   0 | SELECT STATEMENT               |                |

|   1 |  MERGE JOIN SEMI               |                |

|   2 |   TABLE ACCESS BY INDEX ROWID  | T_TABLES       |

|   3 |    INDEX FULL SCAN             | T_TABLES_IDX1  |

|   4 |   SORT UNIQUE                  |                |

|   5 |    BITMAP CONVERSION TO ROWIDS |                |

|   6 |     BITMAP INDEX FAST FULL SCAN| T_OBJECTS_IDX4 |

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

NO_SEMIJOIN

语法:NO_SEMIJOIN([<子查询块>])

描述:禁止优化器使用半关联.

HELLODBA.COM>exec sql_explain('select * from t_tables t where exists (select /*+NO_SEMIJOIN*/1 from t_objects o where t.owner=o.owner)', 'BASIC');

 

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

| Id  | Operation                    | Name           |

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

|   0 | SELECT STATEMENT             |                |

|   1 |  FILTER                      |                |

|   2 |   TABLE ACCESS FULL          | T_TABLES       |

|   3 |   BITMAP CONVERSION TO ROWIDS|                |

|   4 |    BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX4 |

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

SEMIJOIN

语法:SEMIJOIN([<子查询块>])

描述:指示优化器使用半关联,关联方式由优化器自己决定

HELLODBA.COM>exec sql_explain('select * from t_tables t where exists (select /*+SEMIJOIN*/1 from t_objects o where t.owner=o.owner)', 'BASIC');

 

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

| Id  | Operation                     | Name           |

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

|   0 | SELECT STATEMENT              |                |

|   1 |  HASH JOIN SEMI               |                |

|   2 |   TABLE ACCESS FULL           | T_TABLES       |

|   3 |   BITMAP CONVERSION TO ROWIDS |                |

|   4 |    BITMAP INDEX FAST FULL SCAN| T_OBJECTS_IDX4 |

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

SEMIJOIN_DRIVER

语法:SEMIJOIN_DRIVER([<子查询块>])

描述:指示优化器在使用普通关联获取半关联结果时,首先驱动哪个子查询;

HELLODBA.COM>exec sql_explain('SELECT /*+ SEMIJOIN_DRIVER(@inv1) */* FROM t_datafiles d where tablespace_name = ANY (select /*+ qb_name(inv1) */ tablespace_name from t_tablespaces ts) and file_id = ANY (select /*+ qb_name(inv2) */user_id from t_users u)', 'BASIC');

 

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

| Id  | Operation           | Name            |

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

|   0 | SELECT STATEMENT    |                 |

|   1 |  NESTED LOOPS       |                 |

|   2 |   NESTED LOOPS      |                 |

|   3 |    TABLE ACCESS FULL| T_DATAFILES     |

|   4 |    INDEX UNIQUE SCAN| T_TABLESPACE_PK |

|   5 |   INDEX UNIQUE SCAN | T_USERS_PK      |

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

SWAP_JOIN_INPUTS

语法:SWAP_JOIN_INPUTS([<@查询块>] <1> [<2> ...])

描述:指示优化器允许交换表的哈希关联数据输入顺序。

HELLODBA.COM>exec sql_explain('select /*+ leading(t) SWAP_JOIN_INPUTS(o) */* from t_tables t, t_objects o where t.owner=o.owner and t.table_name=o.object_name','BASIC OUTLINE COST');

Plan hash value: 2796668393

 

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

| Id  | Operation          | Name      | Cost (%CPU)|

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

|   0 | SELECT STATEMENT   |           |   696   (3)|

|   1 |  HASH JOIN         |           |   696   (3)|

|   2 |   TABLE ACCESS FULL| T_OBJECTS |   297   (3)|

|   3 |   TABLE ACCESS FULL| T_TABLES  |    28   (4)|

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

NO_SWAP_JOIN_INPUTS

语法:NO_SWAP_JOIN_INPUTS([<@查询块>] <1> [<2> ...])

描述:禁止优化器允许交换表的哈希关联数据输入顺序。

示例(11.2.0.1):

HELLODBA.COM>exec sql_explain('select /*+NO_SWAP_JOIN_INPUTS(t@inv)*/distinct object_name from t_objects o where object_name not in (select /*+qb_name(inv)*/table_name from t_tables t)', 'TYPICAL OUTLINE');

 

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

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

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

|   0 | SELECT STATEMENT       |                |  2691 |   120K|       |   320   (6)| 00:00:04 |

|   1 |  HASH UNIQUE           |                |  2691 |   120K|       |   320   (6)| 00:00:04 |

|*  2 |   HASH JOIN ANTI SNA   |                | 67645 |  3038K|  2608K|   309   (2)| 00:00:04 |

|   3 |    INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | 72116 |  1760K|       |   185   (2)| 00:00:02 |

|   4 |    INDEX FAST FULL SCAN| T_TABLES_PK    |  2696 | 56616 |       |     5   (0)| 00:00:01 |

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

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat