SQL优化一例：注意视图对性能的影响

近日在生产库ADDM中捕捉到了一些列SQL语句，绝大部分通过索引优化都能够搞定，但是如下一句SQL语句却非常难缠。通过索引优化效果一直不理想，最后进一步分析得出该语句上层视图用了×查询出了ACH大数据表的所有字段（数百万记录，50个字段的表），而我们在SQL中只用到了区区几个字段，于是决定抛弃该视图，把视图中相关逻辑直接在上层SQL中展现。通过该动作，大大降低了对关键表的查询开销，得以高效优化。

1. 问题SQL语句： 
2. Select Count(Distinct e.Entry_Id) 
3. From v_ACE e 
4. Where e.Agent_Code = :B8 And Not Exists 
5.  (Select Ed.Entry_Id From AED Ed Where e.Entry_Id = Ed.Entry_Id And Ed.Deleted_Flag = '0') And 
6.             (:B7 Is Null Or e.Entry_Id = :B7) And (:B6 Is Null Or e.Container_Num = :B6) And (:B5 = '0' Or e.Decl_Port = :B5) And 
7.             (:B4 Is Null Or Trunc(e.d_Date) >= :B3) And (:B2 Is Null Or Trunc(e.d_Date) <= :B1) 
8.  
9. 我们看看视图：v_ACE 
10. create or replace view v_ACE as 
11. select tmp.*, 
12. status.cus_operation_status, 
13. status.control_type, 
14. status.data_source, 
15. status.customs_mode, 
16. status.predigest_mode, 
17. status.goods_type, 
18. status.risk_type, 
19. status.control_date, 
20. status.e_supervision_type 
21. from 
22. (select rownum as id,head.*,con.container_num 
23.   from AEC t 
24.          inner join AEH head on t.entry_id = head.entry_id 
25.          inner join AC con on t.container_id = con.id and head.deleted_flag='0' and con.deleted_flag='0')tmp 
26.          left join AESN status on tmp.entry_id=status.entry_id order by tmp.id asc 
27.  
28. -------------------------------------------------------------------------------- 
29. 执行计划 
30. -------------------------------------------------------------------------------- 
31. [Execution Plan Information] 
32.   
33. ------------------------------------------------------------------------------------------------------------------------ 
34. | Operation                                                    | PHV/Object Name               |  Rows | Bytes| Cost   | 
35. ------------------------------------------------------------------------------------------------------------------------ 
36. |000[000]SELECT STATEMENT                                       |---- 2192985071.0 ----         |       |      |  10761 | 
37. |001[001]SORT GROUP BY                                          |                               |     1 |   89 |        | 
38. |002[002] NESTED LOOPS OUTER                                    |                               |     1 |   89 |  10761 | 
39. |003[003]  HASH JOIN RIGHT ANTI                                 |                               |     1 |   70 |  10760 | 
40. |004[004]   INDEX FAST FULL SCAN                                |IDX_A_E_C                      |   685K|   13M|    841 | 
41. |005[004]   VIEW                                                |                               |   386K|   18M|   7675 | 
42. |006[005]    COUNT                                              |                               |       |      |        | 
43. |007[006]     HASH JOIN                                         |                               |   386K|   29M|   7675 | 
44. |008[007]      INDEX FAST FULL SCAN                             |IDX_TEST3                      |   780K|   14M|    830 | 
45. |009[007]      HASH JOIN                                        |                               |   385K|   22M|   4289 | 
46. |010[008]       INDEX FAST FULL SCAN                            |IDX_A_E_H                      |   362K|   13M|   1346 | 
47. |011[008]       TABLE ACCESS FULL                               |AEC                            |   757K|   15M|    766 | 
48. |012[003]  INDEX UNIQUE SCAN                                    |PK_A_E_S_N                     |     1 |   19 |      1 | 
49. ------------------------------------------------------------------------------------------------------ 
50.   
51.  
52. -------------------------------------------------------------------------------- 
53. 优化方法 
54. -------------------------------------------------------------------------------- 
55. 1、新建索引 
56. create index IDX_ADCPHC_CONTAINER_3 on ADCPHC_CONTAINER (DELETED_FLAG, ID, CONTAINER_NUM) 
57.   tablespace TBL_INDEX 
58.   pctfree 10 
59.   initrans 2 
60.   maxtrans 255 
61.   storage 
62.   ( 
63.     initial 64K 
64.     minextents 1 
65.     maxextents unlimited 
66.   ); 
67.  
68. 2、重写SQL语句 
69. Select Count(Distinct head.entry_id) 
70. from AEC t 
71. inner join A_E_H head on t.entry_id = head.entry_id and head.deleted_flag='0' 
72. And head.Agent_Code = :B8  
73. And (:B7 Is Null Or head.Entry_Id = :B7)  
74. And (:B5 = '0' Or head.Decl_Port = :B5)  
75. And (:B4 Is Null Or Trunc(head.d_Date) >= :B3)  
76. And (:B2 Is Null Or Trunc(head.d_Date) <= :B1) 
77. inner join AC con on t.container_id = con.id  and con.deleted_flag='0' 
78. And (:B6 Is Null Or con.Container_Num = :B6)  
79. left join AESN status on head.entry_id=status.entry_id 
80. And Not Exists 
81. (Select Ed.Entry_Id From AED Ed Where head.Entry_Id = Ed.Entry_Id And Ed.Deleted_Flag = '0'     ) 
82.  
83. -------------------------------------------------------------------------------- 
84. 预计优化后执行计划--COST由10761提升到1462，在索引优化的基础上效率提升7倍 
85. -------------------------------------------------------------------------------- 
86. SELECT STATEMENT, GOAL = ALL_ROWS                               Cost=1462   Cardinality=1   Bytes=81 
87.  SORT GROUP BY              Cardinality=1   Bytes=81 
88.   NESTED LOOPS OUTER                                        Cost=1462   Cardinality=1   Bytes=81 
89.    NESTED LOOPS                                         Cost=1460   Cardinality=1   Bytes=81 
90.     NESTED LOOPS                                        Cost=1458   Cardinality=1   Bytes=62 
91.      INDEX FAST FULL SCAN   Object owner=OPERATION  Object name=IDX_A_E_H_M         Cost=1456   Cardinality=1   Bytes=40 
92.      INDEX RANGE SCAN       Object owner=OPERATION  Object name=IDX_A_E_C           Cost=2      Cardinality=1   Bytes=22 
93.     INDEX RANGE SCAN        Object owner=OPERATION  Object name=IDX_TEST3           Cost=2      Cardinality=1   Bytes=19 
94.    VIEW Object owner=SYS                                    Cost=2      Cardinality=1    
95.     FILTER                   
96.      INDEX UNIQUE SCAN      Object owner=OPERATION  Object name=PK_A_E_S_N          Cost=2      Cardinality=1   Bytes=19 
97.      INDEX RANGE SCAN       Object owner=OPERATION  Object name=IDX_ADCPHC_ENTRY_DECLARE    Cost=3      Cardinality=1   Bytes=21