Oracle SQL Hints --- Join Hints

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 |