当前位置: 代码迷 >> Oracle开发 >> 一个查询语句的效率有关问题
  详细解决方案

一个查询语句的效率有关问题

热度:115   发布时间:2016-04-24 07:40:17.0
一个查询语句的效率问题
sql语句如下:

SELECT a.activity_ins_name, c.procid,
  MAX ( TO_DATE (a.completed_time, 'yyyy-mm-dd hh24:mi:ss')
  - TO_DATE (a.created_time, 'yyyy-mm-dd hh24:mi:ss')
  )
  * 24
  * 60
  * 60 AS max_time,
  MIN ( TO_DATE (a.completed_time, 'yyyy-mm-dd hh24:mi:ss')
  - TO_DATE (a.created_time, 'yyyy-mm-dd hh24:mi:ss')
  )
  * 24
  * 60
  * 60 AS min_time,
  AVG ( TO_DATE (a.completed_time, 'yyyy-mm-dd hh24:mi:ss')
  - TO_DATE (a.created_time, 'yyyy-mm-dd hh24:mi:ss')
  )
  * 24
  * 60
  * 60 AS avg_time,
  COUNT (*) AS flow_count
  FROM t_workitem_his a INNER JOIN t_process_def_manage c ON a.process_def_id =
  c.process_id
  INNER JOIN tfas_r_company_process d ON d.procid = c.procid
  WHERE d.company_code = 'A3201'
  AND a.completed_time >= '2006-01-01'
  AND a.completed_time <= '2010-04-07'
GROUP BY a.activity_ins_name, c.procid;

表结构如下:
CREATE TABLE T_WORKITEM_HIS
(
  WORKITEM_ID NUMBER(10) NOT NULL,
  M_ID NUMBER(10),
  PROCESS_DEF_ID NUMBER(10),
  PROCESS_DEF_NAME VARCHAR2(40 BYTE),
  PROCESS_INS_ID NUMBER(10),
  PROCESS_INS_NAME VARCHAR2(40 BYTE),
  ACTIVITY_INS_ID NUMBER(10),
  ACTIVITY_INS_NAME VARCHAR2(40 BYTE),
  TYPE NUMBER(1),
  CREATED_TIME CHAR(19 BYTE),
  PARTICIPANT VARCHAR2(10 BYTE),
  START_TIME CHAR(19 BYTE),
  TIME_UNIT VARCHAR2(10 BYTE),
  TIME_LIMIT NUMBER(10),
  ATTACHMENT_ID NUMBER(10),
  FORM_ID NUMBER(10),
  APP_ID NUMBER(10),
  ACTIVITY_LIST VARCHAR2(100 BYTE),
  COMPLETED_TIME CHAR(20 BYTE),
  DESCRIPTION VARCHAR2(400 BYTE),
  STATE NUMBER(2),
  PRESTATE NUMBER(2)
)
CREATE TABLE T_PROCESS_DEF_MANAGE
(
  PROCID VARCHAR2(40 BYTE) NOT NULL,
  MODEL_ID NUMBER(10) NOT NULL,
  PROCESS_ID NUMBER(10) NOT NULL,
  MODEL_NAME VARCHAR2(50 BYTE) NOT NULL,
  PROCESS_NAME VARCHAR2(50 BYTE) NOT NULL,
  FORMAL_FLAG NUMBER(1) NOT NULL,
  CHECKOUT_FLAG NUMBER(1),
  PROCESS_DESC VARCHAR2(300 BYTE),
  OPEN_FLAG NUMBER(1),
  STATE_FLAG NUMBER(1),
  PROCESS_MOD_FLAG NUMBER(10),
  CHECKOUT_USER VARCHAR2(20 BYTE),
  PROCESS_FILE_PATH VARCHAR2(100 BYTE) NOT NULL,
  PROCESS_TYPE VARCHAR2(50 BYTE)
)
CREATE TABLE TFAS_R_COMPANY_PROCESS
(
  COMPANY_CODE VARCHAR2(100 BYTE) NOT NULL,
  PROCID VARCHAR2(40 BYTE) NOT NULL
)
t_workitem_his表的数据量在500万左右,其他两张表的数据较少,不超过1000条,针对t_workitem_his表的ACTIVITY_INS_ID,PARTICIPANT,PROCESS_INS_ID,PROCESS_DEF_ID,ACTIVITY_INS_NAME,COMPLETED_TIME,CREATED_TIME
这几个字段已建立索引,其他两张表的用于关联的字段也已做索引。但查询耗时还是很大,大概30秒左右,请问有什么方法提高效率吗?



------解决方案--------------------
--第一个方案
create index idx_1 on t_workitem_his(process_def_id, completed_time, activity_ins_name, created_time);

--第二个方案
create index idx_1 on t_workitem_his(completed_time, process_def_id, activity_ins_name, created_time);

执行,看看效率和执行计划:

--加了index(a idx_1) 提示,目的是为了扫描整个索引而不是表。这个索引就相当于字段少的一个小表

select /*+ use_hash(c d a) leading(c d a) index(a idx_1) */
a.activity_ins_name,
d.procid,
count(1) max_time,
count(1) avg_time,
count(1) flow_count
from t_process_def_manage c, tfas_r_company_process d, t_workitem_his a
 where d.procid = c.procid
and d.company_code = 'A3201'
and c.process_id = a.process_def_id
and a.completed_time >= '2006-01-01'
and a.completed_time <= '2010-04-07'
 group by a.activity_ins_name, d.procid


------解决方案--------------------
建议将原先的process_def_id列的索引改为process_def_id与completed_time的复合索引,其次加大db_cache_size,还有就是将语句改为
SELECT a.activity_ins_name, c.procid,
MAX ( TO_DATE (a.completed_time, 'yyyy-mm-dd hh24:mi:ss')
- TO_DATE (a.created_time, 'yyyy-mm-dd hh24:mi:ss')
)
* 24
* 60
* 60 AS max_time,
MIN ( TO_DATE (a.completed_time, 'yyyy-mm-dd hh24:mi:ss')
- TO_DATE (a.created_time, 'yyyy-mm-dd hh24:mi:ss')
)
* 24
* 60
* 60 AS min_time,
AVG ( TO_DATE (a.completed_time, 'yyyy-mm-dd hh24:mi:ss')
- TO_DATE (a.created_time, 'yyyy-mm-dd hh24:mi:ss')
)
* 24
* 60
* 60 AS avg_time,
COUNT (*) AS flow_count
FROM t_workitem_his a ,
(select c.procid, c.process_id from t_process_def_manage c INNER JOIN tfas_r_company_process d ON d.procid = c.procid where d.company_code = 'A3201') c
WHERE a.process_def_id = c.process_id
AND a.completed_time >= '2006-01-01'
AND a.completed_time <= '2010-04-07'
GROUP BY a.activity_ins_name, c.procid;
  相关解决方案