oracle中外关联的解惑(三)
09
----------------------------------------------------------
10
Plan hash value: 195079442
11
12
---------------------------------------------------------------------------
13
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
14
---------------------------------------------------------------------------
15
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (17)| 00:00:01 |
16
|* 1 | HASH JOIN | | 1 | 42 | 6 (17)| 00:00:01 |
17
|* 2 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
18
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
19
---------------------------------------------------------------------------
20
21
Predicate Information (identified by operation id):
22
---------------------------------------------------
23
24
1 - access("P1"."A"="P3"."A3")
25
2 - filter("P3"."B3"='2')
出乎预料了，虽然过滤了子表，但是变成了普通的hash关联。这是为什么呢？猜测是：外关联一般是先关联后过滤的，但是为了效率，简化处理，把普通过滤提前。主表过滤，记录中子表有可能为空，所以保留外关联；子表过滤，那么子表对应的主表一定是普通关联上的记录，所以简化为普通关联。根据这个思路，那么is null的过滤条件应该会出现在外关联以后，看例子：
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p3.b3 is null;
02 www.2cto.com
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
4 s d 4
06
6 s
07
7 d
08
8 d
09
10
11
执行计划
12
----------------------------------------------------------
13
Plan hash value: 2883136784
14
15
----------------------------------------------------------------------------
16
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
17
----------------------------------------------------------------------------
18
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
19
|* 1 | FILTER | | | | | |
20
|* 2 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
21
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
22
| 4 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
23
----------------------------------------------------------------------------
24
25
Predicate Information (identified by operation id):
26
---------------------------------------------------
27
28
1 - filter("P3"."B3" IS NULL)
29
2 - access("P1"."A"="P3"."A3"(+))
果然和预想的一样，先外关联，再判断is null的条件。
3、在where条件中添加关联条件，结果将会如何？
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p1.b=p3.b3;
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
1 1 1 1 1 1
06
2 2 2 2 2 2
07
3 3 3 3 3 3
08
09
10
执行计划
11
----------------------------------------------------------
12
Plan hash value: 195079442
13 www.2cto.com
14
---------------------------------------------------------------------------
15
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
16
---------------------------------------------------------------------------
17
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (17)| 00:00:01 |
18
|* 1 | HASH JOIN | | 1 | 42 | 6 (17)| 00:00:01 |
19
| 2 | TABLE ACCESS FULL| P3