oracle中外关联的解惑(二)
-------
30
31
Predicate Information (identified by operation id):
32
---------------------------------------------------
33
34
4 - filter("P1"."A"="P3"."A3" AND "P1"."B"='s')
结果不是我们以为会对主表做过滤，而是对子表的过滤，含义是：外关联的时候，除了"P1"."A"="P3"."A3"的条件外，还要判断关联的主表记录"P1"."B"='s'是否成立，如果成立则关联子表记录，否则添加空记录。而且从hash外关联变成了nested loops外关联。
3、那么，如果在on中写子表的过滤条件，又会怎么样呢？
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 and p3.b3='2';
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
2 2 2 2 2 2
06
1 1 1
07
6 s
08
3 3 3
09
5 f f
10
4 s d
11
7 d
12
8 d
13
14
已选择8行。
15
16
17
执行计划
18
----------------------------------------------------------
19
Plan hash value: 3306833788
20
21
---------------------------------------------------------------------------
22
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
23
---------------------------------------------------------------------------
24
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
25
|* 1 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
26
| 2 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
27
|* 3 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
28
---------------------------------------------------------------------------
29
30
Predicate Information (identified by operation id):
31
---------------------------------------------------
32
33
1 - access("P1"."A"="P3"."A3"(+))
34
3 - filter("P3"."B3"(+)='2')
这个结果才是我们想要的，是先对子表做了过滤，然后在做hash外关联。
结论：on中的所有条件只对子表过滤和关联，不影响主表的记录。
二、left join的where条件：
1、如果在where中写主表的过滤条件。
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p1.b='s';
02 www.2cto.com
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
4 s d 4
06
6 s
07
08
09
执行计划
10
----------------------------------------------------------
11
Plan hash value: 3306833788
12
13
---------------------------------------------------------------------------
14
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
15
---------------------------------------------------------------------------
16
| 0 | SELECT STATEMENT | | 2 | 84 | 6 (17)| 00:00:01 |
17
|* 1 | HASH JOIN OUTER | | 2 | 84 | 6 (17)| 00:00:01 |
18
|* 2 | TABLE ACCESS FULL| P1 | 2 | 42 | 2 (0)| 00:00:01 |
19
| 3 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
20
---------------------------------------------------------------------------
21
22
Predicate Information (identified by operation id):
23
---------------------------------------------------
24
25
1 - access("P1"."A"="P3"."A3"(+))
26
2 - filter("P1"."B"='s')
先过滤主表，然后在hash外关联，和预想的一样。
2、在where条件中过滤子表。
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p3.b3='2';
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
2 2 2 2 2 2
06
07
08
执行计划