一个查询语句的效率问题

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秒左右，请问有什么方法提高效率吗？

解决方案 »

  1.   

    把三表完整的DDL贴出来，包括索引、主键。或者贴出你的执行计划。
      

  2.   

    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)
    )
    TABLESPACE CWFZ
    PCTUSED    0
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          1218M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    LOGGING 
    NOCOMPRESS 
    NOCACHE
    NOPARALLEL
    MONITORING;
    CREATE UNIQUE INDEX PK_T_WORKITEM_HIS ON T_WORKITEM_HIS
    (WORKITEM_ID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          272M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX IDX_ACTIVITY_INS_ID ON T_WORKITEM_HIS
    (ACTIVITY_INS_ID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX IDX_PARTICIPANT ON T_WORKITEM_HIS
    (PARTICIPANT)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          144M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX IDX_PROCESS_INS_ID ON T_WORKITEM_HIS
    (PROCESS_INS_ID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          176M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX PK_PROCESS_DEF_ID ON T_WORKITEM_HIS
    (PROCESS_DEF_ID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX PK_ACTIVITY_INS_NAME ON T_WORKITEM_HIS
    (ACTIVITY_INS_NAME)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX IDX_COMPLETED_TIME ON T_WORKITEM_HIS
    (COMPLETED_TIME)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX IDX_CREATE_TIME ON T_WORKITEM_HIS
    (CREATED_TIME)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    ALTER TABLE T_WORKITEM_HIS ADD (
      CONSTRAINT PK_T_WORKITEM_HIS PRIMARY KEY (WORKITEM_ID)
        USING INDEX 
        TABLESPACE CWFZ
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    (
                    INITIAL          272M
                    MINEXTENTS       1
                    MAXEXTENTS       2147483645
                    PCTINCREASE      0
                   ));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)
    )
    TABLESPACE CWFZ
    PCTUSED    0
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          256K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    LOGGING 
    NOCOMPRESS 
    NOCACHE
    NOPARALLEL
    MONITORING;
    CREATE UNIQUE INDEX PK_T_PROCESS_DEF_MANAGE ON T_PROCESS_DEF_MANAGE
    (PROCID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    CREATE INDEX TP_PROCESS_ID ON T_PROCESS_DEF_MANAGE
    (PROCESS_ID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    ALTER TABLE T_PROCESS_DEF_MANAGE ADD (
      CONSTRAINT PK_T_PROCESS_DEF_MANAGE PRIMARY KEY (PROCID)
        USING INDEX 
        TABLESPACE CWFZ
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       2147483645
                    PCTINCREASE      0
                   ));CREATE TABLE TFAS_R_COMPANY_PROCESS
    (
      COMPANY_CODE  VARCHAR2(100 BYTE)              NOT NULL,
      PROCID        VARCHAR2(40 BYTE)               NOT NULL
    )
    TABLESPACE CWFZ
    PCTUSED    0
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          128K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    LOGGING 
    NOCOMPRESS 
    NOCACHE
    NOPARALLEL
    MONITORING;
    CREATE UNIQUE INDEX PK_TFAS_R_COMPANY_PROCESS ON TFAS_R_COMPANY_PROCESS
    (COMPANY_CODE, PROCID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;
    ALTER TABLE TFAS_R_COMPANY_PROCESS ADD (
      CONSTRAINT PK_TFAS_R_COMPANY_PROCESS PRIMARY KEY (COMPANY_CODE, PROCID)
        USING INDEX 
        TABLESPACE CWFZ
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    (
                    INITIAL          128K
                    MINEXTENTS       1
                    MAXEXTENTS       2147483645
                    PCTINCREASE      0
                   ));
      

  3.   


    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=ALL_ROWS 274 K   40680                           
      HASH GROUP BY 274 K 30 M 40680                           
        HASH JOIN 306 K 34 M 33068                           
          INDEX RANGE SCAN TMSFZWF.PK_TFAS_R_COMPANY_PROCESS 49   1 K 2                           
          HASH JOIN 5 M 480 M 32970                           
            TABLE ACCESS FULL TMSFZWF.T_PROCESS_DEF_MANAGE 889   23 K 8                           
            TABLE ACCESS FULL TMSFZWF.T_WORKITEM_HIS 5 M 338 M 32866                           
      

  4.   

    --这个是多少行？
    select count(1)
      from t_process_def_manage c, tfas_r_company_process d 
     where d.procid = c.procid
       and d.company_code = 'A3201'
      

  5.   

    TABLE ACCESS FULL TMSFZWF.T_PROCESS_DEF_MANAGE 889 23 K 8   
      TABLE ACCESS FULL TMSFZWF.T_WORKITEM_HIS 5 M 338 M 32866  
    这里考虑下如果允许  加索引如果两表数据量较大  在join前通过条件把各数据集先缩小
      

  6.   

    select count(1)
      from t_process_def_manage c, tfas_r_company_process d  
     where d.procid = c.procid
      and d.company_code = 'A3201'
    这条查询结果是30行
      

  7.   

    a.completed_time的列上建个TO_DATE函数索引试试。
      

  8.   

    建了一个，貌似没什么用，查询时没有按to_date查询啊
      

  9.   

    --我觉得可以优化的地方就是给t_process_def_manage表加一个复合索引，这个索引要达到：
    --1）快速全部扫描这个索引（index fast full scan)而不是全表扫描（FULL TABLE SCAN）
    --2）避免group by 的排序
    --如果能建一个这样的索引，效率要好很多--先看一下这个，要多久？ 把执行计划帖出来select /*+ use_hash(c d a) leading(c d a) */
           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
      

  10.   

    给t_process_def_manage表的PROCID, PROCESS_ID字段加一个复合索引。
    CREATE INDEX TMSFZWF.IDX_PROCESS_2 ON TMSFZWF.T_PROCESS_DEF_MANAGE
    (PROCID, PROCESS_ID)
    LOGGING
    TABLESPACE CWFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;执行上面的代码还是要20秒。
    执行计划如下：如何避免对t_workitem_his表的全连接呢？这个表数据量大，对process_def_id和activity_ins_name的单独索引已经建立，是要对那几个字段建复合索引吗？
      

  11.   

    t_workitem_his 做成分区   按月或按周 看需求是什么时间段本地分区索引 completed_time 
      

  12.   

    SELECT /*+parallel(a,4)*/
    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;开起并行查询功能
      

  13.   

    --第一个方案
    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
      

  14.   

    建议将原先的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;
      

  15.   

    使用codearts提供的第一种方案，将查询时间缩减到7秒
    使用bobfang提供的新sql语句后，查询时间缩减到5秒
    问题解决，结贴。多谢各位大大帮忙！
      

  16.   

    将where 条件总的d.company_code = 'A3201'写到最后再看看