| 0 | SELECT STATEMENT | | 1 | 48 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 48 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| USER_INFO | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEV_INFO | 1 | 24 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."USER_ID"="B"."USER_ID")
2.排序合并连接(SORT MERGE JOIN)
SQL> select /*+ use_merge(a b) */ a.user_name, b.dev_no
from user_info a, dev_info b
where a.user_id > b.user_id; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 261599494
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 48 | 6 (34)| 00:00:01
|
| 1 | MERGE JOIN | | 1 | 48 | 6 (34)| 00:00:01
|
| 2 | SORT JOIN | | 1 | 24 | 3 (34)| 00:00:01
|
| 3 | TABLE ACCESS FULL| USER_INFO | 1 | 24 | 2 (0)| 00:00:01
|
|* 4 | SORT JOIN | | 1 | 24 | 3 (34)| 00:00:01
|
| 5 | TABLE ACCESS FULL| DEV_INFO | 1 | 24 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
--------------------------------------------------- www.2cto.com
4 - access(INTERNAL_FUNCTION("A"."USER_ID")>INTERNAL_FUNCTION("B"."USE
R_ID"))
filter(INTERNAL_FUNCTION("A"."USER_ID")>INTERNAL_FUNCTION("B"."USE
R_ID"))
3.哈希连接(HASH JOIN)
SQL> select a.user_name, b.dev_no
from user_info a, dev_info b
where a.user_id = b.user_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1657342960
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 48 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| USER_INFO | 1 | 24 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEV_INFO | 1 | 24 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."USER_ID"="B"."USER_ID")