在系统负载高的时候抓出一条SQL,1分23秒左右出152行
select a.Case_ID,
a.Case_Type_ID,
a.Case_Subject,
a.Case_Content,
a.Create_Employee_ID,
a.Case_Create_Time,
a.Customer_ID,
a.Dept_ID,
a.Lock_Employee_ID,
a.Order_ID,
a.Order_Type_ID,
a.Pri,
a.Customer_Name,
a.Operate_Time,
a.Case_Require_Finish_Time,
a.Case_Status,
b.Employee_ID,
b.Case_Op_Finish_Time,
c.Case_Type_Name,
d.Employee_Name,
e.Employee_Name as Lock_Employee,
f.Dept_Name,
g.Position_Name,
h.Dept_Name as Create_Dept_Name,
F_GETURGENCYORDER(a.Order_ID) IsUrgency
from t_S_Case a
left join t_S_Case_Operation_Record b
on a.Case_ID = b.Case_Op_ID
left join t_S_Employee d
on d.employee_id = a.create_employee_id
left join t_S_Employee e
on e.employee_id = a.Lock_Employee_ID
left join t_S_Department f
on a.Dept_ID = f.dept_id
left join t_S_Department h
on d.Dept_ID = h.dept_id
inner join t_SD_Case_Type c
on a.Case_Type_ID = c.Case_Type_ID
left join t_SD_Emplyee_Position g
on g.Position_ID = a.Position_ID
where a.Case_Status = 0
and a.Position_ID IN (6017)
order by IsUrgency desc, a.Case_Create_Time, a.Pri Desc
Select * From Table(dbms_xplan.display)
Plan hash value: 3249879879
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19273 | 5778K| | 4401 (4)| 00:00:53 |
| 1 | SORT ORDER BY | | 19273 | 5778K| 12M| 4401 (4)| 00:00:53 |
|* 2 | HASH JOIN RIGHT OUTER | | 19273 | 5778K| | 3121 (5)| 00:00:38 |
| 3 | TABLE ACCESS FULL | T_S_EMPLOYEE | 2018 | 28252 | | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 19273 | 5514K| | 3111 (5)| 00:00:38 |
| 5 | TABLE ACCESS FULL | T_S_DEPARTMENT | 84 | 1764 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 19273 | 5119K| | 3105 (5)| 00:00:38 |
| 7 | TABLE ACCESS FULL | T_S_EMPLOYEE | 2018 | 36324 | | 8 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 19273 | 4780K| | 3096 (5)| 00:00:38 |
| 9 | TABLE ACCESS FULL | T_S_DEPARTMENT | 84 | 1764 | | 5 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 19273 | 4385K| | 3090 (5)| 00:00:38 |
| 11 | TABLE ACCESS FULL | T_SD_CASE_TYPE | 17 | 323 | | 6 (0)| 00:00:01 |
|* 12 | HASH JOIN OUTER | | 19273 | 4027K| 3920K| 3082 (5)| 00:00:37 |
| 13 | MERGE JOIN OUTER | | 19273 | 3688K| | 754 (1)| 00:00:10 |
|* 14 | TABLE ACCESS BY INDEX ROWID | T_S_CASE | 19273 | 3425K| | 752 (1)| 00:00:10 |
|* 15 | INDEX RANGE SCAN | IDX_T_S_CASE_STATUS | 51528 | | | 33 (4)| 00:00:01 |
|* 16 | SORT JOIN | | 1 | 14 | | 2 (50)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| T_SD_EMPLYEE_POSITION | 1 | 14 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_T_SD_EMPLYEE_POSITION | 1 | | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | T_S_CASE_OPERATION_RECORD | 589K| 10M| | 1257 (6)| 00:00:16 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."EMPLOYEE_ID"(+)="A"."LOCK_EMPLOYEE_ID")
4 - access("D"."DEPT_ID"="H"."DEPT_ID"(+))
6 - access("D"."EMPLOYEE_ID"(+)="A"."CREATE_EMPLOYEE_ID")
8 - access("A"."DEPT_ID"="F"."DEPT_ID"(+))
10 - access("A"."CASE_TYPE_ID"="C"."CASE_TYPE_ID")
12 - access("A"."CASE_ID"="B"."CASE_OP_ID"(+))
14 - filter("A"."POSITION_ID"=6017)
15 - access("A"."CASE_STATUS"=0)
16 - access("G"."POSITION_ID"(+)="A"."POSITION_ID")
filter("G"."POSITION_ID"(+)="A"."POSITION_ID")
18 - access("G"."POSITION_ID"(+)=6017)
====================================================================
分析:
Select Count(*) From t_S_Case Where Case_Status = 0 --56869
Select Count(*) From t_S_Case Where Position_ID IN (6017) --783131
这两个列上面都有索引,执行计划用的是Case_Status上面的索引,我的第一
反应是将这个两个列建立个组合索引,但是后面发现case_status已经存在
于另外一个组合索引里面了,要同时用上这个两个索引访问一张表,只能用
AND_EQUAL的Hints了,CBO中一般是不会在同一张表上选中两个索引一起去
访问的。
====================================================================
加Hints的SQL,6秒出结果
select /*+ AND_EQUAL(a IDX_T_S_CASE_STATUS IDX_T_S_CASE_POSITION) */
a.Case_ID,
a.Case_Type_ID,
a.Case_Subject,
a.Case_Content,
a.Create_Employee_ID,
a.Case_Create_Time,
a.Customer_ID,
a.Dept_ID,
a.Lock_Employee_ID,
a.Order_ID,
a.Order_Type_ID,
a.Pri,
a.Customer_Name,
a.Operate_Time,
a.Case_Require_Finish_Time,
a.Case_Status,
b.Employee_ID,
b.Case_Op_Finish_Time,
c.Case_Type_Name,
d.Employee_Name,
e.Employee_Name as Lock_Employee,
f.Dept_Name,
g.Position_Name,
h.Dept_Name as Create_Dept_Name,
F_GETURGENCYORDER(a.Order_ID) IsUrgency
from t_S_Case a
left join t_S_Case_Operation_Record b
on a.Case_ID = b.Case_Op_ID
left join t_S_Employee d
on d.employee_id = a.create_employee_id
left join t_S_Employee e
on e.employee_id = a.Lock_Employee_ID
left join t_S_Department f
on a.Dept_ID = f.dept_id
left join t_S_Department h
on d.Dept_ID = h.dept_id
inner join t_SD_Case_Type c
on a.Case_Type_ID = c.Case_Type_ID
left join t_SD_Emplyee_Position g
on g.Position_ID = a.Position_ID
where a.Case_Status = 0
and a.Position_ID IN (6017)
order by IsUrgency desc, a.Case_Create_Time, a.Pri Desc
Plan hash value: 3864087155
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19273 | 5778K| | 9431 (2)| 00:01:54 |
| 1 | SORT ORDER BY | | 19273 | 5778K| 12M| 9431 (2)| 00:01:54 |
|* 2 | HASH JOIN RIGHT OUTER | | 19273 | 5778K| | 8151 (2)| 00:01:38 |
| 3 | TABLE ACCESS FULL | T_S_EMPLOYEE | 2018 | 28252 | | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 19273 | 5514K| | 8142 (2)| 00:01:38 |
| 5 | TABLE ACCESS FULL | T_S_DEPARTMENT | 84 | 1764 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 19273 | 5119K| | 8136 (2)| 00:01:38 |
| 7 | TABLE ACCESS FULL | T_S_EMPLOYEE | 2018 | 36324 | | 8 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 19273 | 4780K| | 8126 (2)| 00:01:38 |
| 9 | TABLE ACCESS FULL | T_S_DEPARTMENT | 84 | 1764 | | 5 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 19273 | 4385K| | 8120 (2)| 00:01:38 |
| 11 | TABLE ACCESS FULL | T_SD_CASE_TYPE | 17 | 323 | | 6 (0)| 00:00:01 |
|* 12 | HASH JOIN OUTER | | 19273 | 4027K| 3920K| 8113 (2)| 00:01:38 |
| 13 | MERGE JOIN OUTER | | 19273 | 3688K| | 5784 (1)| 00:01:10 |
|* 14 | TABLE ACCESS BY INDEX ROWID | T_S_CASE | 19273 | 3425K| | 5782 (1)| 00:01:10 |
| 15 | AND-EQUAL | | | | | | |
|* 16 | INDEX RANGE SCAN | IDX_T_S_CASE_STATUS | 51528 | | | 33 (4)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX_T_S_CASE_POSITION | 762K| | | 413 (4)| 00:00:05 |
|* 18 | SORT JOIN | | 1 | 14 | | 2 (50)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID| T_SD_EMPLYEE_POSITION | 1 | 14 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_T_SD_EMPLYEE_POSITION | 1 | | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | T_S_CASE_OPERATION_RECORD | 589K| 10M| | 1257 (6)| 00:00:16 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."EMPLOYEE_ID"(+)="A"."LOCK_EMPLOYEE_ID")
4 - access("D"."DEPT_ID"="H"."DEPT_ID"(+))
6 - access("D"."EMPLOYEE_ID"(+)="A"."CREATE_EMPLOYEE_ID")
8 - access("A"."DEPT_ID"="F"."DEPT_ID"(+))
10 - access("A"."CASE_TYPE_ID"="C"."CASE_TYPE_ID")
12 - access("A"."CASE_ID"="B"."CASE_OP_ID"(+))
14 - filter("A"."CASE_STATUS"=0 AND "A"."POSITION_ID"=6017)
16 - access("A"."CASE_STATUS"=0)
17 - access("A"."POSITION_ID"=6017)
18 - access("G"."POSITION_ID"(+)="A"."POSITION_ID")
filter("G"."POSITION_ID"(+)="A"."POSITION_ID")
20 - access("G"."POSITION_ID"(+)=6017)
====================================================================
快是快了,但是SQL是拼出来的,不一定每次就是这两个列,还有一种方法,
将原来的那个组合索引删除,原来的组合索引由CASE_STATUS,DEPT_ID组成
建立下面组合索引
Create Index ind_zu_case_dept_pos On T_S_CASE(CASE_STATUS,DEPT_ID,POSITION_ID) Nologging Online
索引建立建立好之后并没有用上,仍然用的是CASE_STATUS列上的IDX_T_S_CASE_STATUS,我将其也删除
Drop Index IDX_T_S_CASE_STATUS
然后再次运行,组合索引就可以使用了,难到是组合索引的前导列不能再单独
存在索引。
最后的执行计划如下,然后是不到1秒出结果:
Plan hash value: 3915948668
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20799 | 6215K| | 5225 (3)| 00:01:03 |
| 1 | SORT ORDER BY | | 20799 | 6215K| 13M| 5225 (3)| 00:01:03 |
|* 2 | HASH JOIN RIGHT OUTER | | 20799 | 6215K| | 3848 (4)| 00:00:47 |
| 3 | TABLE ACCESS FULL | T_S_EMPLOYEE | 2018 | 28252 | | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 20799 | 5930K| | 3838 (4)| 00:00:47 |
| 5 | TABLE ACCESS FULL | T_S_DEPARTMENT | 84 | 1764 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 20799 | 5504K| | 3832 (4)| 00:00:46 |
| 7 | TABLE ACCESS FULL | T_S_EMPLOYEE | 2018 | 36324 | | 8 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 20799 | 5138K| | 3822 (4)| 00:00:46 |
| 9 | TABLE ACCESS FULL | T_S_DEPARTMENT | 84 | 1764 | | 5 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 20799 | 4712K| | 3816 (4)| 00:00:46 |
| 11 | TABLE ACCESS FULL | T_SD_CASE_TYPE | 17 | 323 | | 6 (0)| 00:00:01 |
|* 12 | HASH JOIN OUTER | | 20799 | 4326K| 4208K| 3809 (4)| 00:00:46 |
| 13 | MERGE JOIN OUTER | | 20799 | 3960K| | 1466 (1)| 00:00:18 |
| 14 | TABLE ACCESS BY INDEX ROWID | T_S_CASE | 20799 | 3676K| | 1464 (1)| 00:00:18 |
|* 15 | INDEX RANGE SCAN | IND_ZU_CASE_DEPT_POS | 21600 | | | 41 (3)| 00:00:01 |
|* 16 | SORT JOIN | | 1 | 14 | | 2 (50)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| T_SD_EMPLYEE_POSITION | 1 | 14 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_T_SD_EMPLYEE_POSITION | 1 | | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | T_S_CASE_OPERATION_RECORD | 589K| 10M| | 1257 (6)| 00:00:16 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."EMPLOYEE_ID"(+)="A"."LOCK_EMPLOYEE_ID")
4 - access("D"."DEPT_ID"="H"."DEPT_ID"(+))
6 - access("D"."EMPLOYEE_ID"(+)="A"."CREATE_EMPLOYEE_ID")
8 - access("A"."DEPT_ID"="F"."DEPT_ID"(+))
10 - access("A"."CASE_TYPE_ID"="C"."CASE_TYPE_ID")
12 - access("A"."CASE_ID"="B"."CASE_OP_ID"(+))
15 - access("A"."CASE_STATUS"=0 AND "A"."POSITION_ID"=6017)
filter("A"."POSITION_ID"=6017)
16 - access("G"."POSITION_ID"(+)="A"."POSITION_ID")
filter("G"."POSITION_ID"(+)="A"."POSITION_ID")
18 - access("G"."POSITION_ID"(+)=6017)
AND_EQUAL和组合索引优化一条SQL
最新推荐文章于 2021-04-04 20:42:07 发布