Oracle SQL Hints --- Join Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 04:09:09

Share to 

Join Hints NL_AJ Usage: NL_AJ([<@SubBlock>]) Description: Instructs the optimizer to perform Nested Loop Anti-join with the table in sub-query.  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 Usage: HASH_AJ([<@SubBlock>]) Description: Instructs the optimizer to perform Hash Anti-join with the table in sub-query.  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 Usage: MERGE_AJ([<@SubBlock>]) Description: Instructs the optimizer to perform Merge Anti-join with the table in sub-query. 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 Usage: USE_HASH([<@Block>] <Table1> [<Table2>]) Description: instructs the optimizer to use hash- join to join each specified table. Combine with LEADING hint to indicate the join order. 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 Usage: NO_USE_HASH([<@Block>] <Table1> [<Table2> ...]) Description: instructs the optimizer not use the specified tables as the inner table in hash join. If all tables to be joined are specified in the hint, the optimizer will not consider hash join. 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 Usage: USE_MERGE([<@Block>] <Table1> [<Table2>]) Description: instructs the optimizer to use Merge- join to join each specified table. Combine with LEADING hint to indicate the join order. 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 Usage: NO_USE_MERGE([<@Block>] <Table1> [<Table2> ...]) Description: instructs the optimizer not use the specified tables as the inner table in merge join. If all tables to be joined are specified in the hint, the optimizer will not consider merge join. 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 Usage: USE_NL([<@Block>] <Table1> [<Table2>]) Description: instructs the optimizer to use Nested-Loop-join to join each specified table. Combine with LEADING hint to indicate the join order. 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 Usage: USE_NL_WITH_INDEX([<@Block>] <Table> [索引1 ...]) or USE_NL_WITH_INDEX([<@Block>] <Table> [(<Index字段列表1>) ...]) Description: instructs the optimizer to use the specified table as the inner table in Nested-Loop-join, and use the specified index to access to the table. 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 Usage: NO_USE_NL([<@Block>] <Table1> [<Table2> ...]) Description: instructs the optimizer not use the specified tables as the inner table in nested-loop join. If all tables to be joined are specified in the hint, the optimizer will not consider nested-loop join. 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 Usage: USE_MERGE_CARTESIANUSE_SEMI([<@Block>] <Table1> [<Table2>]) Description: instructs the optimizer to use Merge Cartesian Join to join each specified table. Demo (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 Usage: LEADING([<@Block>] <Table1> [<Table2> ... ]) Description: instructs the optimizer to use specified sequence to join tables Refer to the demo of USE_HASH USE_ANTI Usage: USE_ANTII([<@Block>] <Table1> [<Table2>]) Description: instructs the optimizer to use anti-join, can only be observed in the internal statement of parallel anti-join query. Demo (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 Usage: USE_SEMI([<@Block>] <Table1> [<Table2>]) Description: instructs the optimizer to use semi-join, can only be observed in the internal statement of parallel semi-join query. Demo (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 Usage: NATIVE_FULL_OUTER_JOIN([<@Block>]) Description: instructs the optimizer to use native full out 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 Usage: NO_NATIVE_FULL_OUTER_JOIN([<@Block>]) Description: Prevents the optimizer to use native full out 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 Usage: NO_CARTESIAN([<@block>] <Table1> [<Table2> ...]) Description: Prevents the optimizer to use Cartesian Join to join each specified table. 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 Usage: ORDERED                      Description: instructs the optimizer to join tables in the order of their appearance in FROM clause. 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 Usage: PX_JOIN_FILTER(<Table>) Description: Instructs the optimizer to use bitmap filter to perform hash outer join or parallel join. 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 Usage: NO_PX_JOIN_FILTER(<Table>) Description: Prevents the optimizer to use bitmap filter to perform hash outer join or parallel join. 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 Usage: NL_SJ([<@SubBlock>]) Description: Instructs the optimizer to perform Nested Loop Semi-join with the table in sub-query.  . 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 Usage: HASH_SJ([<@SubBlock>]) Description: Instructs the optimizer to perform Hash Semi-join with the table in sub-query.. 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 Usage: MERGE_SJ([<@SubBlock>]) Description: Instructs the optimizer to perform Merge Semi-join with the table in sub-query. 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 Usage: NO_SEMIJOIN([<@SubBlock>]) Description: Prevent the optimizer to perform Semi-join with the table in sub-query. 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 Usage: SEMIJOIN([<@SubBlock>]) Description: Instructs the optimizer to perform any type of Semi-join with the table in sub-query. 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 Usage: SEMIJOIN_DRIVER([<@SubBlock>]) Description: Instructs the optimizer to use the specified sub-query as driver when performing Semi-join with. 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 Usage: SWAP_JOIN_INPUTS([<@Block>] <Table1> [<Table2> ...]) Description: Allow the optimizer to swap tables when performing hash-join. 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 Usage: NO_SWAP_JOIN_INPUTS([<@Block>] <Table1> [<Table2> ...]) Description: Not allow the optimizer to swap tables when performing hash-join. Demo (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 | -------------------------------------------------------------------------------------------------