1 row selected.
Execution Plan
---------------------------------------------------------- Plan hash value: 3168189658
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 00:00:01 |
|* 3 | TABLE ACCESS FULL | A | 1 | 00:00:01 |
| 4 | BUFFER SORT | | 1 | 00:00:01 |
|* 5 | INDEX RANGE SCAN | ID_B_OBJECT_ID | 1 | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter(\
5 - access(\
Statistics
---------------------------------------------------------- 92 recursive calls 0 db block gets 134 consistent gets 23 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 12 sorts (memory) 0 sorts (disk)
9 | 4 9 | 9 | 4 4 | 3 5 | 1 5 | 1 (0)| | (0)| (0)| (0)| (0)| 1 rows processed
SQL>
发现执行计划并没有使用nested loop和hash join，不过走索引后，执行代价明显减少。Merge join发生了排序，如果内存够用还好，不够用就比较耗时了。
强制hash
A表驱动
SQL> Select /*+ use_hash(a,b) */count(*) from a,b where a.id=b.object_id 2 And a.id=53;
COUNT(*) ---------- 1
1 row selected.
Execution Plan
---------------------------------------------------------- Plan hash value: 895278611
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN | | 1 | 9 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| A | 1 | 4 | 3 (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): ---------------------------------------------------
2 - access(\ 3 - filter(\
4 - access(\
Statistics
---------------------------------------------------------- 1 recursive calls 0 db block gets 5 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>
--强制使用hash join，a表默认变为了驱动表，执行代价很低，符合要求
B表驱动
SQL> Select /*+ ordered use_hash(b) */count(*) from a,b where a.id=b.object_id 2 And a.id=53;
COUNT(*) ---------- 1
1 row selected.
Execution Plan
---------------------------------------------------------- Plan hash value: 895278611
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN | | 1 | 9 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| A | 1 | 4 | 3 (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): ---------------------------------------------------
2 - access(\ 3 - filter(\
4 - access(\
Statistics
---------------------------------------------------------- 1 recursive calls 0 db block gets 5 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>
发现有索引，并且有统计信息的情况下，无法强制B表作为驱动表，oracle对hint进行了忽略。
删除统计信息试试：
SQL> EXEC dbms_stats.delete_table_stats(user,'B',cascade_parts => TRUE);
PL/SQL procedure successfully completed
SQL> EXEC dbms_stats.delete_table_stats(user,'A',cascade_parts
=>TRUE);
PL/SQL procedure successfully completed SQL>
--测试发现仍然不能将B表作为驱动表，修改optimizer_mode为rule alter session set optimizer_mode=rule;
SQL> Select /*+ ordered use_nl(b) */count(*) from a,b where a.id=b.object_id 2 Andobject_id=53;
--发现仍然不能将B表作为驱动表
强制nested loop
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.
Execution Plan
---------------------------------------------------------- Plan hash value: 1183094437
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| A | 1 | 13 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ID_B_OBJECT_ID | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------