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;