我写得一个视图v_yy_dksjtjb，其中有2层的嵌套，大量的数据处理，非常复杂。记录总数31万多条。语句1：
select * from v_yy_dksjtjb t where dkdm like 'LP3A01'
执行耗时0.9秒
语句2：
select * from v_yy_dksjtjb t where dkdm='LP3A01'
执行耗时82秒！
语句3：
select * from v_yy_dksjtjb t where dkdm in ('LP3A01')
执行耗时73秒！这是为什么？如何能进行优化？
select * from v_yy_dksjtjb t where dkdm like 'LP3A01'
执行耗时0.9秒
语句2：
select * from v_yy_dksjtjb t where dkdm='LP3A01'
执行耗时82秒！
语句3：
select * from v_yy_dksjtjb t where dkdm in ('LP3A01')
执行耗时73秒！这是为什么？如何能进行优化？
sqlplus登上去，
set autotrace on
之后执行这三条语句。
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 2 | 786 | | 2752 (2)|
6 | 1 | VIEW | V_YY_DKSJTJB | 2 | 786 | | 2752 (2)|
7 | 2 | WINDOW SORT | | 2 | 684 | | 2752 (2)|
8 | 3 | HASH JOIN OUTER | | 2 | 684 | | 2751 (2)|
9 | 4 | NESTED LOOPS | | 2 | 656 | | 2737 (2)|
10 | 5 | TABLE ACCESS BY INDEX ROWID | YY_DKJCSJ | 1 | 11 | | 2 (0)|
11 | 6 | INDEX UNIQUE SCAN | DKJCSJ_DKDM | 1 | | | 1 (0)|
12 | 7 | VIEW | | 2 | 634 | | 2735 (2)|
13 | 8 | WINDOW SORT | | 957 | 223K| | 2735 (2)|
14 | 9 | WINDOW SORT | | 957 | 223K| | 2735 (2)|
15 | 10 | VIEW | V_YY_DKYDYCL | 957 | 223K| | 2733 (2)|
16 | 11 | SORT ORDER BY | | 957 | 246K| | 2733 (2)|
17 | 12 | HASH JOIN RIGHT OUTER | | 957 | 246K| | 2732 (2)|
18 | 13 | VIEW | | 2 | 324 | | 15 (7)|
19 | 14 | SORT GROUP BY | | 2 | 154 | | 15 (7)|
20 | 15 | NESTED LOOPS | | 2 | 154 | | 14 (0)|
21 | 16 | NESTED LOOPS | | 2 | 94 | | 10 (0)|
22 | 17 | VIEW | | 1 | 13 | | 2 (0)|
23 | 18 | COUNT | | | | | |
24 | 19 | CONNECT BY WITHOUT FILTERING | | | | | |
25 | 20 | FAST DUAL | | 1 | | | 2 (0)|
26 | 21 | TABLE ACCESS FULL | YY_YJSJLR | 2 | 68 | | 8 (0)|
27 | 22 | TABLE ACCESS BY INDEX ROWID | YD_DBA04 | 1 | 30 | | 2 (0)|
28 | 23 | INDEX UNIQUE SCAN | SYS_C0015580 | 1 | | | 1 (0)|
29 | 24 | HASH JOIN RIGHT OUTER | | 957 | 97614 | | 2716 (2)|
30 | 25 | VIEW | | 1 | 66 | | 10 (10)|
31 | 26 | SORT GROUP BY | | 1 | 64 | | 10 (10)|
32 | 27 | NESTED LOOPS | | 1 | 64 | | 9 (0)|
33 | 28 | NESTED LOOPS | | 1 | 42 | | 7 (0)|
34 | 29 | VIEW | | 1 | 13 | | 2 (0)|
35 | 30 | COUNT | | | | | |
36 | 31 | CONNECT BY WITHOUT FILTERING| | | | | |
37 | 32 | FAST DUAL | | 1 | | | 2 (0)|
38 | 33 | TABLE ACCESS FULL | YY_SJSJLR | 1 | 29 | | 5 (0)|
39 | 34 | TABLE ACCESS BY INDEX ROWID | YD_DBA05 | 1 | 22 | | 2 (0)|
40 | 35 | INDEX UNIQUE SCAN | SYS_C0015601 | 1 | | | 1 (0)|
41 | 36 | NESTED LOOPS | | 957 | 34452 | | 2706 (2)|
42 | 37 | TABLE ACCESS BY INDEX ROWID | YY_DKJCSJ | 1 | 24 | | 2 (0)|
43 | 38 | INDEX UNIQUE SCAN | DKJCSJ_DKDM | 1 | | | 1 (0)|
44 | 39 | VIEW | | 957 | 11484 | | 2704 (2)|
45 | 40 | NESTED LOOPS | | 584K| 6852K| | 2704 (2)|
46 | 41 | INDEX UNIQUE SCAN | DKJCSJ_DKDM | 1 | 7 | | 1 (0)|
47 | 42 | VIEW | | 584K| 2855K| | 2703 (2)|
48 | 43 | VIEW | | 584K| 2855K| | 2703 (2)|
49 | 44 | SORT UNIQUE | | 584K| 3997K| 17M| 2703 (26)|
50 | 45 | UNION-ALL | | | | | |
51 | 46 | TABLE ACCESS FULL | YY_DBA04_DK | 439K| 3004K| | 515 (1)|
52 | 47 | TABLE ACCESS FULL | YY_DBA05_DK | 145K| 992K| | 167 (2)|
53 | 48 | TABLE ACCESS FULL | YD_DAB09 | 2585 | 36190 | | 13 (0)|
54 -----------------------------------------------------------------------------------------------------------
55
56 Note
57 -----
58 - 'PLAN_TABLE' is old version
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 584K| 219M| | 139K (1)|
6 | 1 | VIEW | V_YY_DKSJTJB | 584K| 219M| | 139K (1)|
7 | 2 | WINDOW SORT | | 584K| 190M| 397M| 139K (1)|
8 | 3 | HASH JOIN RIGHT OUTER | | 584K| 190M| | 96941 (1)|
9 | 4 | TABLE ACCESS FULL | YD_DAB09 | 2585 | 36190 | | 13 (0)|
10 | 5 | MERGE JOIN | | 584K| 182M| | 96924 (1)|
11 | 6 | VIEW | | 584K| 176M| | 96915 (1)|
12 | 7 | WINDOW SORT | | 584K| 133M| 315M| 96915 (1)|
13 | 8 | WINDOW SORT | | 584K| 133M| 315M| 96915 (1)|
14 | 9 | VIEW | V_YY_DKYDYCL | 584K| 133M| | 36546 (1)|
15 | 10 | SORT ORDER BY | | 584K| 147M| 338M| 36546 (1)|
16 | 11 | HASH JOIN RIGHT OUTER | | 584K| 147M| | 3269 (2)|
17 | 12 | VIEW | | 127 | 20574 | | 412 (1)|
18 | 13 | HASH GROUP BY | | 127 | 9779 | | 412 (1)|
19 | 14 | NESTED LOOPS | | 127 | 9779 | | 411 (0)|
20 | 15 | NESTED LOOPS | | 208 | 9776 | | 10 (0)|
21 | 16 | VIEW | | 1 | 13 | | 2 (0)|
22 | 17 | COUNT | | | | | |
23 | 18 | CONNECT BY WITHOUT FILTERING | | | | | |
24 | 19 | FAST DUAL | | 1 | | | 2 (0)|
25 | 20 | TABLE ACCESS FULL | YY_YJSJLR | 208 | 7072 | | 8 (0)|
26 | 21 | TABLE ACCESS BY INDEX ROWID | YD_DBA04 | 1 | 30 | | 2 (0)|
27 | 22 | INDEX UNIQUE SCAN | SYS_C0015580 | 1 | | | 1 (0)|
28 | 23 | HASH JOIN RIGHT OUTER | | 584K| 56M| | 2853 (2)|
29 | 24 | VIEW | | 49 | 3234 | | 134 (1)|
30 | 25 | HASH GROUP BY | | 49 | 3136 | | 134 (1)|
31 | 26 | NESTED LOOPS | | 49 | 3136 | | 133 (0)|
32 | 27 | NESTED LOOPS | | 63 | 2646 | | 7 (0)|
33 | 28 | VIEW | | 1 | 13 | | 2 (0)|
34 | 29 | COUNT | | | | | |
35 | 30 | CONNECT BY WITHOUT FILTERING| | | | | |
36 | 31 | FAST DUAL | | 1 | | | 2 (0)|
37 | 32 | TABLE ACCESS FULL | YY_SJSJLR | 63 | 1827 | | 5 (0)|
38 | 33 | TABLE ACCESS BY INDEX ROWID | YD_DBA05 | 1 | 22 | | 2 (0)|
39 | 34 | INDEX UNIQUE SCAN | SYS_C0015601 | 1 | | | 1 (0)|
40 | 35 | HASH JOIN | | 584K| 20M| | 2716 (2)|
41 | 36 | TABLE ACCESS FULL | YY_DKJCSJ | 611 | 14664 | | 8 (0)|
42 | 37 | VIEW | | 584K| 6852K| | 2704 (2)|
43 | 38 | NESTED LOOPS | | 584K| 6852K| | 2704 (2)|
44 | 39 | INDEX UNIQUE SCAN | DKJCSJ_DKDM | 1 | 7 | | 1 (0)|
45 | 40 | VIEW | | 584K| 2855K| | 2703 (2)|
46 | 41 | VIEW | | 584K| 2855K| | 2703 (2)|
47 | 42 | SORT UNIQUE | | 584K| 3997K| 17M| 2703 (26)|
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 584K| 219M| | 139K (1)|
6 | 1 | VIEW | V_YY_DKSJTJB | 584K| 219M| | 139K (1)|
7 | 2 | WINDOW SORT | | 584K| 190M| 397M| 139K (1)|
8 | 3 | HASH JOIN RIGHT OUTER | | 584K| 190M| | 96941 (1)|
9 | 4 | TABLE ACCESS FULL | YD_DAB09 | 2585 | 36190 | | 13 (0)|
10 | 5 | MERGE JOIN | | 584K| 182M| | 96924 (1)|
11 | 6 | VIEW | | 584K| 176M| | 96915 (1)|
12 | 7 | WINDOW SORT | | 584K| 133M| 315M| 96915 (1)|
13 | 8 | WINDOW SORT | | 584K| 133M| 315M| 96915 (1)|
14 | 9 | VIEW | V_YY_DKYDYCL | 584K| 133M| | 36546 (1)|
15 | 10 | SORT ORDER BY | | 584K| 147M| 338M| 36546 (1)|
16 | 11 | HASH JOIN RIGHT OUTER | | 584K| 147M| | 3269 (2)|
17 | 12 | VIEW | | 127 | 20574 | | 412 (1)|
18 | 13 | HASH GROUP BY | | 127 | 9779 | | 412 (1)|
19 | 14 | NESTED LOOPS | | 127 | 9779 | | 411 (0)|
20 | 15 | NESTED LOOPS | | 208 | 9776 | | 10 (0)|
21 | 16 | VIEW | | 1 | 13 | | 2 (0)|
22 | 17 | COUNT | | | | | |
23 | 18 | CONNECT BY WITHOUT FILTERING | | | | | |
24 | 19 | FAST DUAL | | 1 | | | 2 (0)|
25 | 20 | TABLE ACCESS FULL | YY_YJSJLR | 208 | 7072 | | 8 (0)|
26 | 21 | TABLE ACCESS BY INDEX ROWID | YD_DBA04 | 1 | 30 | | 2 (0)|
27 | 22 | INDEX UNIQUE SCAN | SYS_C0015580 | 1 | | | 1 (0)|
28 | 23 | HASH JOIN RIGHT OUTER | | 584K| 56M| | 2853 (2)|
29 | 24 | VIEW | | 49 | 3234 | | 134 (1)|
30 | 25 | HASH GROUP BY | | 49 | 3136 | | 134 (1)|
31 | 26 | NESTED LOOPS | | 49 | 3136 | | 133 (0)|
32 | 27 | NESTED LOOPS | | 63 | 2646 | | 7 (0)|
33 | 28 | VIEW | | 1 | 13 | | 2 (0)|
34 | 29 | COUNT | | | | | |
35 | 30 | CONNECT BY WITHOUT FILTERING| | | | | |
36 | 31 | FAST DUAL | | 1 | | | 2 (0)|
37 | 32 | TABLE ACCESS FULL | YY_SJSJLR | 63 | 1827 | | 5 (0)|
38 | 33 | TABLE ACCESS BY INDEX ROWID | YD_DBA05 | 1 | 22 | | 2 (0)|
39 | 34 | INDEX UNIQUE SCAN | SYS_C0015601 | 1 | | | 1 (0)|
40 | 35 | HASH JOIN | | 584K| 20M| | 2716 (2)|
41 | 36 | TABLE ACCESS FULL | YY_DKJCSJ | 611 | 14664 | | 8 (0)|
42 | 37 | VIEW | | 584K| 6852K| | 2704 (2)|
43 | 38 | NESTED LOOPS | | 584K| 6852K| | 2704 (2)|
44 | 39 | INDEX UNIQUE SCAN | DKJCSJ_DKDM | 1 | 7 | | 1 (0)|
45 | 40 | VIEW | | 584K| 2855K| | 2703 (2)|
46 | 41 | VIEW | | 584K| 2855K| | 2703 (2)|
47 | 42 | SORT UNIQUE | | 584K| 3997K| 17M| 2703 (26)|
nest loop join: 扫描驱动表(如果有排序,按照 排序字段 排序后),取驱动表的逐行记录与被驱动表对比.
NESTED LOOPS有一个优点是：可以先返回已经
连接的行，而不必等待所有的连接操作处理完才返回数据，
这可以实现快速的响应时间。nest loop比merge快。。