有一张流程任务表叫flow_task,表结构如下,主键是SERIALNO,索引为:(OBJECTNO, OBJECTTYPE, FLOWNO, PHASENO)-- Create table
create table FLOW_TASK
(
SERIALNO VARCHAR2(32) not null,
OBJECTNO VARCHAR2(32),
OBJECTTYPE VARCHAR2(18),
RELATIVESERIALNO VARCHAR2(32),
FLOWNO VARCHAR2(32),
FLOWNAME VARCHAR2(80),
PHASENO VARCHAR2(32),
PHASENAME VARCHAR2(80),
PHASETYPE VARCHAR2(18),
APPLYTYPE VARCHAR2(18),
USERID VARCHAR2(32),
USERNAME VARCHAR2(80),
ORGID VARCHAR2(32),
ORGNAME VARCHAR2(80),
BEGINTIME VARCHAR2(20),
ENDTIME VARCHAR2(20),
PHASECHOICE VARCHAR2(80),
PHASEACTION VARCHAR2(250),
PHASEOPINION CHAR(1600),
PHASEOPINION1 VARCHAR2(18),
PHASEOPINION2 VARCHAR2(400),
PHASEOPINION3 VARCHAR2(400),
PHASEOPINION4 VARCHAR2(250),
CHECKLISTRESULT VARCHAR2(80),
AUTODECISION VARCHAR2(80),
RISKSCANRESULT VARCHAR2(80),
STANDARDTIME1 VARCHAR2(32),
STANDARDTIME2 VARCHAR2(32),
COSTLOB VARCHAR2(32)
)
现在系统中有一条语句是这样的
Select FlowNo,PhaseType,PhaseNo,PhaseName,Count(SerialNo) as WCount from Flow_Task where ObjectType='PlanApply' and UserId='0001' and (EndTime is null or EndTime='') and PhaseNo<>'0010' and PhaseNo<>'3000' Group by FlowNo,PhaseType,Phaseno,PhaseName order by FlowNo,PhaseNo希望对上面这条语句进行优化,现在这张表只有50万条记录,执行上面这条语句要7、8秒时间
create table FLOW_TASK
(
SERIALNO VARCHAR2(32) not null,
OBJECTNO VARCHAR2(32),
OBJECTTYPE VARCHAR2(18),
RELATIVESERIALNO VARCHAR2(32),
FLOWNO VARCHAR2(32),
FLOWNAME VARCHAR2(80),
PHASENO VARCHAR2(32),
PHASENAME VARCHAR2(80),
PHASETYPE VARCHAR2(18),
APPLYTYPE VARCHAR2(18),
USERID VARCHAR2(32),
USERNAME VARCHAR2(80),
ORGID VARCHAR2(32),
ORGNAME VARCHAR2(80),
BEGINTIME VARCHAR2(20),
ENDTIME VARCHAR2(20),
PHASECHOICE VARCHAR2(80),
PHASEACTION VARCHAR2(250),
PHASEOPINION CHAR(1600),
PHASEOPINION1 VARCHAR2(18),
PHASEOPINION2 VARCHAR2(400),
PHASEOPINION3 VARCHAR2(400),
PHASEOPINION4 VARCHAR2(250),
CHECKLISTRESULT VARCHAR2(80),
AUTODECISION VARCHAR2(80),
RISKSCANRESULT VARCHAR2(80),
STANDARDTIME1 VARCHAR2(32),
STANDARDTIME2 VARCHAR2(32),
COSTLOB VARCHAR2(32)
)
现在系统中有一条语句是这样的
Select FlowNo,PhaseType,PhaseNo,PhaseName,Count(SerialNo) as WCount from Flow_Task where ObjectType='PlanApply' and UserId='0001' and (EndTime is null or EndTime='') and PhaseNo<>'0010' and PhaseNo<>'3000' Group by FlowNo,PhaseType,Phaseno,PhaseName order by FlowNo,PhaseNo希望对上面这条语句进行优化,现在这张表只有50万条记录,执行上面这条语句要7、8秒时间
最好给出个执行计划看看,方法是1、解释SQL语句
EXPLAIN PLAN FOR
SELECT .... FROM ... WHERE ...;2、查看执行计划:
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
explain plan for
select FlowNo, PhaseType, PhaseNo, PhaseName, Count(SerialNo) as WorkCount
from FLOW_TASK
where ObjectType = 'CreditApply'
and UserID = '0761'
and (EndTime is null or EndTime = '')
and PhaseNo <> '0010'
and PhaseNo <> '3000'
Group by FlowNo, PhaseType, PhaseNo, PhaseName
Order by FlowNo, PhaseNo
select plan_table_output from table(dbms_xplan.display('plan_Table'));
PLAN_TABLE_OUTPUT
1 Plan hash value: 1415984370
2
3 --------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 --------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 4261 | 312K| 5598 (1)| 00:01:08 |
7 | 1 | SORT GROUP BY | | 4261 | 312K| 5598 (1)| 00:01:08 |
8 |* 2 | TABLE ACCESS FULL| FLOW_TASK | 4261 | 312K| 5597 (1)| 00:01:08 |
9 --------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - filter("ENDTIME" IS NULL AND "USERID"='0761' AND
15 "PHASENO"<>'0010' AND "OBJECTTYPE"='CreditApply' AND "PHASENO"<>'3000')
用到绑定变量了吗?and PhaseNo <>'0010' and PhaseNo <>'3000' 把这句改成 and PhaseNO not in ('0010','3000')
(EndTime is null or EndTime='') 这句用union all来替换一下试试