近日在生产库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 = and head.deleted_flag='0' and con.deleted_flag='0')tmp left join AESN status on tmp.entry_id=status.entry_id order by 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 = 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
