优化乱建索引导致查询性能下降

最新推荐文章于 2021-07-14 14:46:28 发布

ljl_name

最新推荐文章于 2021-07-14 14:46:28 发布

阅读量324

点赞数

分类专栏： oracle SQL优化文章标签：乱建索引导致查询性能下降

本文链接：https://blog.csdn.net/ljl_name/article/details/88301137

版权

oracle 同时被 2 个专栏收录

25 篇文章 0 订阅

订阅专栏

SQL优化

9 篇文章 0 订阅

订阅专栏

语句正常执行很快
加入处方序号i字段后查询后开始缓慢，需执行1063s

通过分析索引选择率不高导致

select count(*) from INQ_D住院费用明细z --120w

select count(*) from INQ_D住院费用明细z where 处方序号i =0 --110w

SQL> set timing on

SQL> explain plan for select nvl(发票序号i, 0) as 发票序号i,
2 nvl(发票序号, '未设置') as 发票序号,
3 发票顺序,
4 收费项目序号i,
5 收费项目序号,
6 规格说明,
7 数量,
8 计量单位,
9 执行单价,
10 执行金额,
11 医保类别,
12 农合类别,
13 套外否,
14 0 As 处方序号I
15 from INQ_VD住院费用明细_发票Z
16 Where 住院序号i = '139'
17 And 处方序号i = 0
18 And 结算序号i > 0
19 and 记账时间 between to_date('2018-10-18 14:06', 'yyyy-MM-dd HH24:mi:ss') and
20 to_date('2018-10-23 23:59', 'yyyy-MM-dd HH24:mi:ss')
21 ;

已解释。

已用时间: 00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3557548714

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 770 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 770 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 680 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 651 | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 347 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 343 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 247 | 0 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 243 | 0 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 204 | 0 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 197 | 0 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 165 | 0 (0)| 00:00:01 |
| 12 | VIEW | index$_join$_003 | 1 | 26 | 0 (0)| 00:00:01 |
|* 13 | HASH JOIN | | | | | |
| 14 | INDEX FAST FULL SCAN | IDX_120671_住院序| 1 | 26 | 0 (0)| 00:00:01 |
| 15 | INDEX FAST FULL SCAN | PK_INQ_D住院费用 | 1 | 26 | 0 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID| INQ_D住院费用明细| 1 | 139 | 0 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX_120677_处方序| 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | DOC_T收费项目价格| 1 | 32 | 0 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_T收费项目价格 | 1 | | 0 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | DOC_T医嘱目录 | 1 | 7 | 0 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_T医嘱目录 | 1 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | INQ_D住院档案Z | 1 | 39 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_INQ_D住院档案Z| 1 | | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_D健康档案 | 1 | 4 | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | DOC_T收费项目 | 1 | 96 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_T收费项目 | 1 | | 0 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PK_T费用类别 | 1 | 4 | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | DOC_L费别价格关联| 1 | 29 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | UK_费别价格关联表| 1 | | 0 (0)| 00:00:01 |
| 30 | VIEW PUSHED PREDICATE | | 1 | 90 | 3 (0)| 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | DOC_L发票对应费用| 1 | 7 | 2 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | IDX_119988_类别序| 1 | | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PK_T发票项目 | 1 | | 0 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | DOC_T发票项目 | 1 | 17 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(TO_DATE('2018-10-23 23:59','yyyy-MM-dd HH24:mi:ss')>=TO_DATE('2018-10-18
14:06','yyyy-MM-dd HH24:mi:ss'))
13 - access(ROWID=ROWID)
16 - filter("A"."住院序号I"=783 AND "A"."结算序号I">0 AND "A"."记账时间">=TO_DATE('2018-10-18
14:06','yyyy-MM-dd HH24:mi:ss') AND "A"."记账时间"<=TO_DATE('2018-10-23 23:59','yyyy-MM-dd
HH24:mi:ss') AND "A"."单据序号I"="Z"."系统序号")
17 - access("A"."处方序号I"=0)
19 - access("C"."系统序号"="A"."收费项目序号I")
20 - filter("A"."打包否B"(+)=1)
21 - access("A"."系统序号"(+)="A"."打包序号I")
23 - access("Z"."住院序号I"="DA"."系统序号")
24 - access("DA"."健康序号I"="JKDA"."系统序号")
26 - access("XM"."系统序号"="C"."项目序号I")
27 - access("L"."系统序号"(+)="XM"."费用类别I")
29 - access("A"."收费项目序号I"="FB"."价格序号I"(+) AND "DA"."病员费别I"="FB"."病人费别I"(+))
34 - access("P2"."类别序号I"="from$_subquery$_024"."QCSJ_C000000002400001")
35 - access("P1"."系统序号"="P2"."发票序号I")
36 - filter("P1"."适用对象N"<>1)

已选择65行。

已用时间: 00: 00: 00.11

INQ_VD住院费用明细_发票Z

explain plan for Select
A.系统序号
,Z.隶属机构i
,A.住院序号i
,Z.婴儿序号i
,A.单据序号I
,A.打包序号I,B.名称 As 打包序号
,A.医嘱目录序号I
,A.医嘱明细序号I
,A.医嘱类型
,FP.发票序号i ,FP.发票序号
,FP.发票顺序
,Z.业务来源N
,Z.业务序号I
,Z.记账单号
,A.收费项目序号I,XM.名称 As 收费项目序号
,XM.规格说明
,XM.费用类别i,L.名称 as 费用类别
,A.记账时间
,A.处方序号I
,A.数量
,XM.计量单位
,A.标准单价
,A.执行单价
,A.执行金额
,A.开单科室I,D.名称 As 开单科室
,A.开单医师r,E.名称 As 开单医师
,A.执行科室I,F.名称 As 执行科室
,A.执行人R,G.名称 As 执行人
,A.执行时间
,A.已退数量
,A.已退金额
,A.冲销状态N,decode(A.冲销状态N,0,'正常',1,'已冲销',2,'已冲销负单') as 冲销状态
,A.冲销序号I
,A.结算序号I
,A.已审核b
,A.审核时间
,A.审核人R
,z.操作人r
,YG.名称操作人
,A.套外否B
,DECODE(A.套外否B,0,'',1,'√','') AS 套外否
,Z.记账方式
,nvl(FB.医保类别,C.医保类别) 医保类别
,nvl(FB.农合类别,C.农合类别) 农合类别
,A.零退原因
,L.系统序号 s
,DECODE(A.处方序号I,0,0,1) as 处方b
,JKDA.健康ID
,A.病人科室I
,Z.操作时间
From INQ_D住院费用明细z A
Inner Join INQ_D住院费用列表z Z On A.单据序号i=Z.系统序号
Inner Join INQ_D住院档案Z DA On Z.住院序号I = DA.系统序号
Inner Join CRM_D健康档案 JKDA On DA.健康序号I = JKDA.系统序号
Left Join DOC_vT收费打包项目 B On B.系统序号 = A.打包序号I
Inner Join DOC_T收费项目价格表 C On C.系统序号 = A.收费项目序号I
Inner Join DOC_T收费项目 XM On XM.系统序号 = C.项目序号i
Left Join DOC_T科室档案 D On D.系统序号 = A.开单科室I
Left Join DOC_T员工档案 E On E.系统序号 = A.开单医师r
Left Join DOC_T科室档案 F On F.系统序号 = A.执行科室I
Left Join DOC_T员工档案 G On G.系统序号 = A.执行人R
Left Join DOC_T费用类别 L On L.系统序号=XM.费用类别i
Left Join DOC_L费别价格关联表 FB On DA.病员费别I = FB.病人费别I and A.收费项目序号I = FB.价格序号I
Left Join (Select p1.系统序号 as 发票序号i,P1.名称 as 发票序号,P1.显示顺序 as 发票顺序,P2.类别序号I From DOC_T发票项目 P1
inner join DOC_L发票对应费用类别 P2 on p1.系统序号=p2.发票序号i And P1.适用对象N<>1) FP On FP.类别序号I=L.系统序号
Left Join Doc_T员工档案 YG On YG.系统序号=Z.操作人R;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1471079152

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1653 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 1653 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 1563 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 1534 | 5 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 1523 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 791 | 5 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 779 | 5 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 767 | 5 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 755 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 659 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 627 | 3 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 605 | 3 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1 | 587 | 3 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 576 | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 565 | 3 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 157 | 3 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | INQ_D住院档案Z | 1 | 39 | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| INQ_D住院费用列表| 1 | 118 | 0 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_120671_住院序| 1 | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | INQ_D住院费用明细| 1 | 408 | 0 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX_120677_单据序| 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案 | 1 | 11 | 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_T员工档案 | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案 | 1 | 11 | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_T员工档案 | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | CRM_D健康档案 | 1 | 18 | 0 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_D健康档案 | 1 | | 0 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | DOC_T医嘱目录 | 1 | 22 | 0 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_T医嘱目录 | 1 | | 0 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | DOC_T收费项目价格| 1 | 32 | 0 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_T收费项目价格 | 1 | | 0 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | DOC_T收费项目 | 1 | 96 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_T收费项目 | 1 | | 0 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | DOC_T费用类别 | 1 | 12 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_T费用类别 | 1 | | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DOC_T科室档案 | 1 | 12 | 0 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_T科室档案 | 1 | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | DOC_T科室档案 | 1 | 12 | 0 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_T科室档案 | 1 | | 0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案 | 1 | 11 | 0 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_T员工档案 | 1 | | 0 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | DOC_L费别价格关联| 1 | 29 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | UK_费别价格关联表| 1 | | 0 (0)| 00:00:01 |
| 43 | VIEW PUSHED PREDICATE | | 1 | 90 | 3 (0)| 00:00:01 |
| 44 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
| 45 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
| 46 | TABLE ACCESS BY INDEX ROWID | DOC_L发票对应费用| 1 | 7 | 2 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | IDX_119988_类别序| 1 | | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | PK_T发票项目 | 1 | | 0 (0)| 00:00:01 |
|* 49 | TABLE ACCESS BY INDEX ROWID | DOC_T发票项目 | 1 | 17 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

18 - access("Z"."住院序号I"="DA"."系统序号")
20 - access("A"."单据序号I"="Z"."系统序号")
22 - access("E"."系统序号"(+)="A"."开单医师R")
24 - access("G"."系统序号"(+)="A"."执行人R")
26 - access("DA"."健康序号I"="JKDA"."系统序号")
27 - filter("A"."打包否B"(+)=1)
28 - access("A"."系统序号"(+)="A"."打包序号I")
30 - access("C"."系统序号"="A"."收费项目序号I")
32 - access("XM"."系统序号"="C"."项目序号I")
34 - access("L"."系统序号"(+)="XM"."费用类别I")
36 - access("D"."系统序号"(+)="A"."开单科室I")
38 - access("F"."系统序号"(+)="A"."执行科室I")
40 - access("YG"."系统序号"(+)="from$_subquery$_023"."QCSJ_C000000000700004")
42 - access("A"."收费项目序号I"="FB"."价格序号I"(+) AND "DA"."病员费别I"="FB"."病人费别I"(+))
47 - access("P2"."类别序号I"="from$_subquery$_023"."QCSJ_C000000002300001")
48 - access("P1"."系统序号"="P2"."发票序号I")
49 - filter("P1"."适用对象N"<>1)

已选择77行。

已用时间: 00: 00: 00.10
SQL>

删除该列索引后语句执行毫秒响应正常

执行计划如下

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 756249470

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 724 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 724 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 634 | 1 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 627 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 314 | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 310 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 214 | 0 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 210 | 0 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 171 | 0 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 164 | 0 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 156 | 0 (0)| 00:00:01 |
| 11 | VIEW | index$_join$_003 | 1 | 26 | 0 (0)| 00:00:01 |
|* 12 | HASH JOIN | | | | | |
| 13 | INDEX FAST FULL SCAN | IDX_120671_住院序| 1 | 26 | 0 (0)| 00:00:01 |
| 14 | INDEX FAST FULL SCAN | PK_INQ_D住院费用 | 1 | 26 | 0 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| INQ_D住院费用明细| 1 | 130 | 0 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_120677_单据序| 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | DOC_T收费项目价格| 1 | 8 | 0 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_T收费项目价格 | 1 | | 0 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | DOC_T医嘱目录 | 1 | 7 | 0 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_T医嘱目录 | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | INQ_D住院档案Z | 1 | 39 | 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_INQ_D住院档案Z| 1 | | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_D健康档案 | 1 | 4 | 0 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | DOC_T收费项目 | 1 | 96 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PK_T收费项目 | 1 | | 0 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_T费用类别 | 1 | 4 | 0 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | UK_费别价格关联表| 1 | 7 | 0 (0)| 00:00:01 |
| 28 | VIEW PUSHED PREDICATE | | 1 | 90 | 3 (0)| 00:00:01 |
| 29 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | DOC_L发票对应费用| 1 | 7 | 2 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX_119988_类别序| 1 | | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PK_T发票项目 | 1 | | 0 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | DOC_T发票项目 | 1 | 17 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

12 - access(ROWID=ROWID)
15 - filter("A"."住院序号I"=710 AND "A"."处方序号I"=0 AND "A"."结算序号I">0)
16 - access("A"."单据序号I"="Z"."系统序号")
18 - access("C"."系统序号"="A"."收费项目序号I")
19 - filter("A"."打包否B"(+)=1)
20 - access("A"."系统序号"(+)="A"."打包序号I")
22 - access("Z"."住院序号I"="DA"."系统序号")
23 - access("DA"."健康序号I"="JKDA"."系统序号")
25 - access("XM"."系统序号"="C"."项目序号I")
26 - access("L"."系统序号"(+)="XM"."费用类别I")
27 - access("A"."收费项目序号I"="FB"."价格序号I"(+) AND "DA"."病员费别I"="FB"."病人费别I"(+))
32 - access("P2"."类别序号I"="from$_subquery$_024"."QCSJ_C000000002400001")
33 - access("P1"."系统序号"="P2"."发票序号I")
34 - filter("P1"."适用对象N"<>1)