以下实验讨论不同连接类型下,各自的限制。即某些场景下只能使用特定的类型。
一、Hash join
1.1 等值的条件下(正常使用HASH JOIN)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_hash(t2) */*
3 from t1,t2
4 where t1.id = t2.t1_id
5 and t1.n = 19;
Explained.
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4069 | 229 (3)| 00:00:03 |
|* 1 | HASH JOIN | | 1 | 4069 | 229 (3)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 102K| 198M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=19)
Note
-----
- dynamic sampling used for this statement
20 rows selected.
1.2 不等值的条件下(不支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_hash(t2) */*
3 from t1,t2
4 where t1.id <> t2.t1_id
5 and t1.n = 19;
Explained.
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 395M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 102K| 395M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 102K| 198M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected.
1.3 大于或小于的条件下(不支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_hash(t2) */*
3 from t1,t2
4 where t1.id > t2.t1_id
5 and t1.n = 19;
Explained.
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5101 | 19M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 5101 | 19M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5101 | 9M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID">"T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected.
1.4 like条件下(不支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_hash(t2) */*
3 from t1,t2
4 where t1.id like t2.t1_id
5 and t1.n = 19;
Explained.
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5101 | 19M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 5101 | 19M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5101 | 9M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))
Note
-----
- dynamic sampling used for this statement
20 rows selected.
二、Merge Short Join
2.1 等值的条件下(正常使用Merge Short Join)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_merge(t2) */*
3 from t1,t2
4 where t1.id = t2.t1_id
5 and t1.n = 19;
Explained.
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 412793182
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4069 | | 43779 (1)| 00:08:46 |
| 1 | MERGE JOIN | | 1 | 4069 | | 43779 (1)| 00:08:46 |
| 2 | SORT JOIN | | 1 | 2028 | | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 2028 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 102K| 198M| 531M| 43775 (1)| 00:08:46 |
| 5 | TABLE ACCESS FULL| T2 | 102K| 198M| | 224 (2)| 00:00:03 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- dynamic sampling used for this statement
23 rows selected.
2.2 不等值的条件下(不支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_merge(t2) */*
3 from t1,t2
4 where t1.id <> t2.t1_id
5 and t1.n = 19;
Explained.
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 395M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 102K| 395M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 102K| 198M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected.
2.3 大于或小于的条件下(支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_merge(t2) */*
3 from t1,t2
4 where t1.id > t2.t1_id
5 and t1.n = 19;
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 412793182
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5101 | 19M| | 43779 (1)| 00:08:46 |
| 1 | MERGE JOIN | | 5101 | 19M| | 43779 (1)| 00:08:46 |
| 2 | SORT JOIN | | 1 | 2028 | | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 2028 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 102K| 198M| 531M| 43775 (1)| 00:08:46 |
| 5 | TABLE ACCESS FULL| T2 | 102K| 198M| | 224 (2)| 00:00:03 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."N"=19)
4 - access(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
filter(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- dynamic sampling used for this statement
23 rows selected.
2.4 like条件下(不支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_merge(t2) */*
3 from t1,t2
4 where t1.id like t2.t1_id
5 and t1.n = 19;
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5101 | 19M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 5101 | 19M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5101 | 9M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))
Note
-----
- dynamic sampling used for this statement
20 rows selected.
三、NESTED LOOPS
3.1 等值的条件下(正常使用NESTED LOOPS)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_nl(t2) */*
3 from t1,t2
4 where t1.id = t2.t1_id
5 and t1.n = 19;
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4069 | 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 1 | 4069 | 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 2041 | 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected.
3.2 不等值的条件下(支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_nl(t2) */*
3 from t1,t2
4 where t1.id <> t2.t1_id
5 and t1.n = 19;
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 395M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 102K| 395M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 102K| 198M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected.
3.3 大于或小于的条件下(支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_nl(t2) */*
3 from t1,t2
4 where t1.id > t2.t1_id
5 and t1.n = 19;
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5101 | 19M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 5101 | 19M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5101 | 9M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID">"T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected.
3.4 like条件下(支持)
SCOTT@ orcl>explain plan for
2 SELECT /*+leading(t1) use_nl(t2) */*
3 from t1,t2
4 where t1.id like t2.t1_id
5 and t1.n = 19;
SCOTT@ orcl>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5101 | 19M| 227 (2)| 00:00:03 |
| 1 | NESTED LOOPS | | 5101 | 19M| 227 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2028 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 5101 | 9M| 224 (2)| 00:00:03 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))
Note
-----
- dynamic sampling used for this statement
20 rows selected.
总结:
各种连接条件限制
等值 | 不等值 | 大于或小于 | like | |
Hash join | ○ | × | × | × |
Merge Short Join | ○ | × | ○ | × |
Nested loops | ○ | ○ | ○ | ○ |
Nested loops支持的所有的SQL连接条件,无任何限制。