oracle中外关联的解惑(五)
d
09
10
11
执行计划
12
----------------------------------------------------------
13
Plan hash value: 2883136784
14 www.2cto.com
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("B3" IS NULL)
29
2 - access("A"="A3"(+))
30
31
SQL> select * from p1,p3 where a=a3(+) and b3 ='2';
32 www.2cto.com
33
A B C A3 B3 C3
34
----- ----- ----- ----- ----- -----
35
2 2 2 2 2 2
36
37
38
执行计划
39
----------------------------------------------------------
40
Plan hash value: 195079442
41
42
---------------------------------------------------------------------------
43
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
44
---------------------------------------------------------------------------
45
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (17)| 00:00:01 |
46
|* 1 | HASH JOIN | | 1 | 42 | 6 (17)| 00:00:01 |
47
|* 2 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
48
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
49
---------------------------------------------------------------------------
50
51
Predicate Information (identified by operation id):
52
---------------------------------------------------
53
54
1 - access("A"="A3")
55
2 - filter("B3"='2')
和left join在where条件中加子表过滤结果一样。
4、那么，和left join 在on中过滤子表一样的是什么？
01
SQL> select * from p1,p3 where a=a3(+) and b3(+) ='2';
02 www.2cto.com
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
--------------------------