一个查询语句的效率问题
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_n