我在10g下试的,好像和表的出现顺序有关啊,from a,c,b和fromb,c,a执行计划不一样
SQL> SELECT /*+ use_hash(a,c) use_nl(c,b) */
2 *
3 FROM a, c, b
4 WHERE 1 = 1
5 AND a.id = c.id
6 AND b.id = c.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1044797622
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 7 (15)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 9 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 6 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 1 | 3 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| C | 1 | 3 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | B | 1 | 3 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="C"."ID")
5 - filter("B"."ID"="C"."ID")
SQL> SELECT /*+ use_hash(a,c) use_nl(c,b) */
2 *
3 FROM b, c, a
4 WHERE 1 = 1
5 AND a.id = c.id
6 AND b.id = c.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 3670128773
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 9 | 7 (15)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 6 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | B | 1 | 3 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 3 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | A | 1 | 3 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | C | 1 | 3 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="C"."ID" AND "B"."ID"="C"."ID")