求助修改一条sql，表数据量较大

这是我写的sql
select m.*, n.bal, f.checkdate
  from grade_dtl n,
       (select t.sys_id, t.acc_input, t.sal_id, max(t.check_date) checkdate
          from mng_dtl t
         where t.txn_type in ('1', '2')
           and t.status_flag = '3'
         group by t.sys_id, t.acc_input, t.sal_id) f,
       (select a.organ_id,
               a.sal_id,
               e.empname,
               b.orgname,
               a.acc,
               a.acc_input,
               a.acc_home,
               a.acc_type,
               c.svt_name,
               a.sal_type,
               sum(a.ave_bal),
               a.sys_id,
               d.v_sys_nm,
               a.svt_no,
               a.cust_name,
               a.open_date
          from grade_dtl a, organ b, pub_svt c, map_sys d, emp e
         where a.sal_id = e.empid
           and a.open_brh_id = b.orgcode
           and a.svt_no = c.svt_no(+)
           and a.sys_id = d.v_sys_id(+)
           and a.txn_dt >= '201001'
           and a.txn_dt <= '201004'
           and a.organ_id in
               (select org
                  from zzjgb o
                 where typeid = '2'
                   and (org = '111901' or parentorg = '111901'))
         group by a.organ_id,
                  e.empname,
                  a.cust_name,
                  a.acc,
                  b.orgname,
                  a.acc_input,
                  a.acc_home,
                  c.svt_name,
                  a.acc_type,
                  a.sal_type,
                  d.v_sys_nm,
                  a.svt_no,
                  a.open_date,
                  a.sal_id,
                  a.sys_id) m
 where m.acc = n.acc
   and m.acc_input = f.acc_input
   and m.sal_id = f.sal_id
   and m.sys_id = f.sys_id
   and n.txn_dt = '201004'
这是2张主要的表，数据量都很大
create table GRADE_DTL
(
  TXN_DT      CHAR(6),
  SAL_ID      CHAR(10),
  ORGAN_ID    CHAR(9),
  SAL_TYPE    CHAR(1),
  SYS_ID      CHAR(2),
  SVT_NO      CHAR(2),
  ACC_INPUT   CHAR(25),
  ACC         CHAR(19),
  ACC_HOME    CHAR(19),
  OPEN_BRH_ID CHAR(9),
  ACC_TYPE    CHAR(1),
  AVE_BAL     NUMBER(12,2),
  BAL         NUMBER(12,2),
  GRADE       NUMBER(12,2),
  CUST_NAME   VARCHAR2(40),
  OPEN_DATE   CHAR(8)
)
create unique index GRADE_DTL_INDEX on GRADE_DTL (TXN_DT, SAL_ID, SYS_ID, ACC_INPUT)create table MNG_DTL
(
  REC_ID       NUMBER(8) not null,
  TXN_DT       CHAR(8),
  TXN_TYPE     CHAR(1),
  SYS_ID       CHAR(2),
  SVT_NO       CHAR(2),
  ACC_INPUT    CHAR(25),
  ACC          CHAR(19),
  ACC_HOME     CHAR(19),
  CUST_NAME    VARCHAR2(40),
  OPEN_BRH_ID  CHAR(9),
  OPEN_DATE    CHAR(8),
  OPEN_CASH    NUMBER(12,2),
  SAL_ID       CHAR(10),
  SAL_NM       CHAR(20),
  ORGAN_ID     CHAR(9),
  OLD_SAL_ID   CHAR(10),
  OLD_SAL_NM   CHAR(20),
  OLD_ORGAN_ID CHAR(9),
  ACT_ORGAN    CHAR(9),
  APPLY_OPR_ID CHAR(10),
  APPLY_OPR_NM CHAR(20),
  CHECK_DATE   CHAR(8),
  CHECK_OPR_ID CHAR(10),
  CHECK_OPR_NM CHAR(20),
  STATUS_FLAG  CHAR(1),
  FAIL_REASON  VARCHAR2(100),
  CHECK_TYPE   CHAR(1)
)
create index MNG_DTL_ACC on MNG_DTL (ACC)
create unique index MNG_DTL_REC on MNG_DTL (REC_ID)
create index MNG_DTL_STA on MNG_DTL (STATUS_FLAG)
create index MNG_DTL_DT_TY_ORG on MNG_DTL (TXN_DT, TXN_TYPE, ACT_ORGAN)
上面2张表 数据量都很大，于是我那样写sql，查询速度很慢
要从grade_dtl表中取ave的某个时间段的合计值，还要从grade_dtl表中取出这个时间段最末时间点bal的值，这些是主要的数据。
然后还要从mng_dtl表中取出一个最大checkdate时间的值，并且要与上面grade_dtl取出的数据相关联。用了3个字段  m.acc_input = f.acc_input
   and m.sal_id = f.sal_id
   and m.sys_id = f.sys_id
求优化

解决方案 »

  1.   

    说下业务逻辑.看sql自己提取太累.
      

  2.   


    select m.*, n.bal, f.checkdate
      from grade_dtl n,
           (select t.sys_id, t.acc_input, t.sal_id, max(t.check_date) checkdate
              from mng_dtl t
             where (t.txn_type = '1' or t.txn_type='2') --in 改成 or
               and t.status_flag = '3'
             group by t.sys_id, t.acc_input, t.sal_id) f,
           (select a.organ_id,
                   a.sal_id,
                   e.empname,
                   b.orgname,
                   a.acc,
                   a.acc_input,
                   a.acc_home,
                   a.acc_type,
                   c.svt_name,
                   a.sal_type,
                   sum(a.ave_bal),
                   a.sys_id,
                   d.v_sys_nm,
                   a.svt_no,
                   a.cust_name,
                   a.open_date
              from grade_dtl a, organ b, pub_svt c, map_sys d, emp e
             where a.sal_id = e.empid
               and a.open_brh_id = b.orgcode
               and a.svt_no = c.svt_no(+)
               and a.sys_id = d.v_sys_id(+)
               and a.txn_dt >= '201001'
               and a.txn_dt <= '201004'
               and exists(    --in 改为exists
                   (select 1 from zzjgb o
                     where typeid = '2'
                       and (org = '111901' or parentorg = '111901')
                       and a.organ_id=org)
             group by a.organ_id,
                      e.empname,
                      a.cust_name,
                      a.acc,
                      b.orgname,
                      a.acc_input,
                      a.acc_home,
                      c.svt_name,
                      a.acc_type,
                      a.sal_type,
                      d.v_sys_nm,
                      a.svt_no,
                      a.open_date,
                      a.sal_id,
                      a.sys_id) m
     where m.acc = n.acc
       and m.acc_input = f.acc_input
       and m.sal_id = f.sal_id
       and m.sys_id = f.sys_id
       and n.txn_dt = '201004'
      

  3.   

    从GRADE_DTL表中
    要合计一段时间内每个账号acc里的ave_bal的数额，
    就是这样a.txn_dt >= '201001' and a.txn_dt <= '201004'这样取一个时间段的合计，我写的sql查询出的一个m表就是这里结果。
    然后还要取这段时间末的时间点的一个bal的数额
    又查询了一次GRADE_DTL表取n.txn_dt = '201004'这个时间点的bal值
    然后从上面的结果中m.acc = n.acc取关联的数据。mng_dtl这个是一个人员sal_id与账号acc建立关系的审核信息表，因为mng_dtl里没有acc的属性，所以后来连接的时候用了3个属性相等
    m.acc_input = f.acc_input
       and m.sal_id = f.sal_id
       and m.sys_id = f.sys_id (select t.sys_id, t.acc_input, t.sal_id, max(t.check_date) checkdate
              from mng_dtl t
             where t.txn_type in ('1', '2')
               and t.status_flag = '3'
             group by t.sys_id, t.acc_input, t.sal_id) f
    f这个是为了，取一个sal_id与acc_input建立关系的审核日期，这个要取最后一天其他几个表基本就是参数表了
    要查的主要结果就是
    人员sal_id   账号acc  总数（sum(ave.bal))  时间段末的数额（bal) 审核日子（checkdate）   
      

  4.   


    12 rows selected.Elapsed: 00:00:21.80Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1762746607--------------------------------------------------------------------------------
    -------------------------| Id  | Operation                     | Name            | Rows  | Bytes |TempSpc
    | Cost (%CPU)| Time     |--------------------------------------------------------------------------------
    -------------------------|   0 | SELECT STATEMENT              |                 |  1380 |   526K|
    | 57331   (1)| 00:13:23 ||*  1 |  HASH JOIN                    |                 |  1380 |   526K|
    | 57331   (1)| 00:13:23 ||   2 |   JOIN FILTER CREATE          | :BF0000         |  1380 |   455K|
    | 41693   (1)| 00:09:44 ||*  3 |    HASH JOIN                  |                 |  1380 |   455K|
    | 41693   (1)| 00:09:44 ||   4 |     VIEW                      |                 |   965 |   250K|
    | 20936   (1)| 00:04:54 ||   5 |      HASH GROUP BY            |                 |   965 |   231K|
    | 20936   (1)| 00:04:54 ||*  6 |       HASH JOIN               |                 |   965 |   231K|
    | 20935   (1)| 00:04:54 ||*  7 |        HASH JOIN              |                 |   965 |   220K|
    | 20863   (1)| 00:04:53 ||   8 |         TABLE ACCESS FULL     | ORGAN           |  2040 | 53040 |
    |    24   (0)| 00:00:01 ||*  9 |         HASH JOIN RIGHT OUTER |                 |   965 |   196K|
    | 20839   (1)| 00:04:52 ||  10 |          TABLE ACCESS FULL    | PUB_SVT         |    39 |  1716 |
    |    22   (0)| 00:00:01 ||* 11 |          HASH JOIN RIGHT OUTER|                 |   965 |   154K|
    | 20816   (1)| 00:04:52 ||  12 |           TABLE ACCESS FULL   | MAP_SYS         |     2 |    34 |
    |    22   (0)| 00:00:01 ||* 13 |           HASH JOIN           |                 |   965 |   138K|
    | 20794   (1)| 00:04:52 ||  14 |            SORT UNIQUE        |                 |    24 |   552 |
    |    24   (0)| 00:00:01 ||* 15 |             TABLE ACCESS FULL | ZZJGB           |    24 |   552 |
    |    24   (0)| 00:00:01 ||* 16 |            TABLE ACCESS FULL  | GRADE_DTL       |   979K|   115M|
    | 20764   (1)| 00:04:51 ||  17 |        TABLE ACCESS FULL      | EMP             | 17742 |   207K|
    |    71   (0)| 00:00:01 ||* 18 |     TABLE ACCESS FULL         | GRADE_DTL       |   979K|    67M|
    | 20752   (1)| 00:04:51 ||  19 |   VIEW                        |                 |  1102K|    55M|
    | 15632   (1)| 00:03:39 ||  20 |    HASH GROUP BY              |                 |  1102K|    49M|    67M
    | 15632   (1)| 00:03:39 ||  21 |     JOIN FILTER USE           | :BF0000         |  1102K|    49M|
    |  7515   (1)| 00:01:46 ||* 22 |      TABLE ACCESS FULL        | MNG_DTL         |  1102K|    49M|
    |  7515   (1)| 00:01:46 |--------------------------------------------------------------------------------
    -------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - access("N"."ACC_INPUT"="F"."ACC_INPUT" AND "N"."SAL_ID"="F"."SAL_ID" AND
                  "N"."SYS_ID"="F"."SYS_ID")
       3 - access("M"."FILE4"="N"."ACC")
       6 - access("E"."EMPID"=TO_NUMBER("A"."SAL_ID"))
       7 - access("A"."OPEN_BRH_ID"="B"."ORGCODE")
       9 - access("A"."SVT_NO"="C"."SVT_NO"(+))
      11 - access("A"."SYS_ID"="D"."V_SYS_ID"(+))
      13 - access("A"."ORGAN_ID"="ORGCODE")
      15 - filter("TOPID"=2 AND ("PARENTORG"='111901' OR "ORG"='111901
    '))  16 - filter("A"."TXN_DT"='201009')
      18 - filter("N"."TXN_DT"='201009')
      22 - filter("T"."STATUS_FLAG"='3' AND ("T"."TXN_TYPE"='1' OR "T"."TXN_TYPE"='2
    ') AND              SYS_OP_BLOOM_FILTER(:BF0000,"T"."ACC_INPUT","T"."SAL_ID","T"."SYS_
    ID"))解释计划
    一般都是查询30秒以上
      

  5.   


    with tmp as ( select t.txn_dt,t.sal_id,t.organ_id,t.sal_type,t.sys_id,t.svt_no,t.acc_input,t.acc,t.acc_home,t.open_brh_id,t.acc_type,t.ave_bal,t.bal,t.grade,t.cust_name,t.open_date
    from grade_dtl t
    where t.txn_dt >= '201001'
               and t.txn_dt <= '201004'
               and t.organ_id in
                   (select orgcode
                      from zzjgb o
                     where topid = '2'
                       and (orgcode = '111901' or parentorg = '111901'))
    )select m.*,
           n.bal file15,
           e.empname file2,
           b.orgname file3,
           c.svt_name file8,
           d.v_sys_nm file11,
           f.checkdate checkdate
      from tmp n,
           (select t.sys_id, t.acc_input, t.sal_id, max(t.check_date) checkdate
              from mng_dtl t
             where t.txn_type in ('1', '2')
               and t.status_flag = '3'
             group by t.sys_id, t.acc_input, t.sal_id) f,
           (select a.organ_id file1,
                   a.sal_id salid,
                   a.open_brh_id,
                   a.acc file4,
                   a.acc_input file5,
                   a.acc_home file6,
                   a.acc_type file7,
                   a.sal_type file9,
                   round(nvl(sum(a.ave_bal), 0) / (4), 2) file10,
                   a.sys_id sysid,
                   a.svt_no file12,
                   a.cust_name file13,
                   a.open_date file14
              from tmp a
             group by a.organ_id,
                      a.cust_name,
                      a.acc,
                      a.open_brh_id,
                      a.acc_input,
                      a.acc_home,
                      a.acc_type,
                      a.sal_type,
                      a.svt_no,
                      a.open_date,
                      a.sal_id,
                      a.sys_id) m,
           organ b,
           pub_svt c,
           map_sys d,
           emp e
     where m.file4 = n.acc
       and n.acc_input = f.acc_input
       and n.sal_id = f.sal_id
       and n.sys_id = f.sys_id
       and m.file4 = n.acc
       and m.salid = e.empid
       and m.open_brh_id = b.orgcode
       and m.file12 = c.svt_no(+)
       and m.sysid = d.v_sys_id(+)
       and n.txn_dt = '201004'昨天改成这样查询，感觉有快了点
      

  6.   

     in oltp system
    this kind of sql makes everyone  be in hell
    be careful