本文共 4356 字，大约阅读时间需要 14 分钟。
近日在生产库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 |
- |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' )
-
-
- 预计优化后执行计划
-
- 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
转载地址：http://yfxxa.baihongyu.com/