Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter(\
4 - access(\ Note -----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
---------------------------------------------------------- 10 recursive calls 0 db block gets 73 consistent gets 1 physical reads 0 redo size
542 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL>
--代价和hash join差不多，另外，即使强制B表作为驱动表，仍然不能将B表作为驱动表。
两个都有索引的情况
SQL> create index id_a_id on a(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'A',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'B',cascade => true);
PL/SQL procedure successfully completed.
SQL>
SQL> Select /*+ ordered use_nl(b) */count(*) from a,b where a.id=b.object_id 2 Andobject_id=53;
COUNT(*) ---------- 1
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
---------------------------------------------------------- Plan hash value: 2751652919
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | NESTED LOOPS | | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| ID_A_ID | 1 | 4 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| ID_B_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access(\ 4 - access(\
Statistics
---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size
542 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL>
--hint强制不能将B表作为驱动表
两个表较小，如果不使用hint，执行计划走sort mergegate方式 代价明显变小，又减少一倍（索引是多么重要）
一个语句使用多个hint的写法
仅举例
SELECT /*+ USE_HASH(TA,TB) parallel(TA,4) parallel(TB,4) */ FROM BSEMPMS TA, BSDPTMS TB
WHERE TA.DPT_NO=TB.DPT_NO;