近日在生产库ADDM中捕捉到了一些列SQL语句，绝大部分通过索引优化都能够搞定，但是如下一句SQL语句却非常难缠。通过索引优化效果一直不理想，最后进一步分析得出该语句上层视图用了×查询出了ACH大数据表的所有字段（数百万记录，50个字段的表），而我们在SQL中只用到了区区几个字段，于是决定抛弃该视图，把视图中相关逻辑直接在上层SQL中展现。通过该动作，大大降低了对关键表的查询开销，得以高效优化。
问题SQL语句：
Select Count(Distinct e.Entry_Id)
From v_ACE e
Where e.Agent_Code = :B8 And Not Exists
(Select Ed.Entry_Id From AED Ed Where e.Entry_Id = Ed.Entry_Id And Ed.Deleted_Flag = '0') And
(: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
(:B4 Is Null Or Trunc(e.d_Date) >= :B3) And (:B2 Is Null Or Trunc(e.d_Date) <= :B1)
我们看看视图：v_ACE
create or replace view v_ACE as
select tmp.*,
status.cus_operation_status,
status.control_type,
status.data_source,
status.customs_mode,
status.predigest_mode,
status.goods_type,
status.risk_type,
status.control_date,
status.e_supervision_type
from
(select rownum as id,head.*,con.container_num
from AEC t
inner join AEH head on t.entry_id = head.entry_id
inner join AC con on t.container_id = con.id and head.deleted_flag='0' and con.deleted_flag='0')tmp
left join AESN status on tmp.entry_id=status.entry_id order by tmp.id asc
--------------------------------------------------------------------------------
执行计划
--------------------------------------------------------------------------------
[Execution Plan Information]
------------------------------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
------------------------------------------------------------------------------------------------------------------------
|000[000]SELECT STATEMENT |---- 2192985071.0 ---- | | | 10761 |
|001[001]SORT GROUP BY | | 1 | 89 | |
|002[002] NESTED LOOPS OUTER | | 1 | 89 | 10761 |
|003[003] HASH JOIN RIGHT ANTI | | 1 | 70 | 10760 |
|004[004] INDEX FAST FULL SCAN |IDX_A_E_C | 685K| 13M| 841 |
|005[004] VIEW | | 386K| 18M| 7675 |
|006[005] COUNT | | | | |
|007[006] HASH JOIN | | 386K| 29M| 7675 |
|008[007] INDEX FAST FULL SCAN |IDX_TEST3 | 780K| 14M| 830 |
|009[007] HASH JOIN | | 385K| 22M| 4289 |
|010[008] INDEX FAST FULL SCAN |IDX_A_E_H | 362K| 13M| 1346 |
|011[008] TABLE ACCESS FULL |AEC | 757K| 15M| 766 |
|012[003] INDEX UNIQUE SCAN |PK_A_E_S_N | 1 | 19 | 1 |
------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
优化方法
--------------------------------------------------------------------------------
1、新建索引
create index IDX_ADCPHC_CONTAINER_3 on ADCPHC_CONTAINER (DELETED_FLAG, ID, CONTAINER_NUM)
tablespace TBL_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
2、重写SQL语句
Select Count(Distinct head.entry_id)
from AEC t
inner join A_E_H head on t.entry_id = head.entry_id and head.deleted_flag='0'
And head.Agent_Code = :B8
And (:B7 Is Null Or head.Entry_Id = :B7)
And (:B5 = '0' Or head.Decl_Port = :B5)
And (:B4 Is Null Or Trunc(head.d_Date) >= :B3)
And (:B2 Is Null Or Trunc(head.d_Date) <= :B1)
inner join AC con on t.container_id = con.id and con.deleted_flag='0'
And (:B6 Is Null Or con.Container_Num = :B6)
left join AESN status on head.entry_id=status.entry_id
And Not Exists
(Select Ed.Entry_Id From AED Ed Where head.Entry_Id = Ed.Entry_Id And Ed.Deleted_Flag = '0' )
--------------------------------------------------------------------------------
预计优化后执行计划--COST由10761提升到1462，在索引优化的基础上效率提升7倍
--------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS Cost=1462 Cardinality=1 Bytes=81
SORT GROUP BY Cardinality=1 Bytes=81
NESTED LOOPS OUTER Cost=1462 Cardinality=1 Bytes=81
NESTED LOOPS Cost=1460 Cardinality=1 Bytes=81
NESTED LOOPS Cost=1458 Cardinality=1 Bytes=62
INDEX FAST FULL SCAN Object owner=OPERATION Object name=IDX_A_E_H_M Cost=1456 Cardinality=1 Bytes=40
INDEX RANGE SCAN Object owner=OPERATION Object name=IDX_A_E_C Cost=2 Cardinality=1 Bytes=22
INDEX RANGE SCAN Object owner=OPERATION Object name=IDX_TEST3 Cost=2 Cardinality=1 Bytes=19
VIEW Object owner=SYS Cost=2 Cardinality=1
FILTER
INDEX UNIQUE SCAN Object owner=OPERATION Object name=PK_A_E_S_N Cost=2 Cardinality=1 Bytes=19
INDEX RANGE SCAN Object owner=OPERATION Object name=IDX_ADCPHC_ENTRY_DECLARE Cost=3 Cardinality=1 Bytes=21