求优化sql,附带解释计划

Question

因为测试的数据很少，不懂要怎么优化了select a1.*, b1.*
  from (select a.acc re_acc,
               round(nvl(sum(case
                               when fund_type = 0 then
                                amt1
                             end),
                         0),
                     2) dsfcg,
               round(nvl(sum(case
                               when fund_type = 0 then
                                amt2
                             end),
                         0),
                     2) bxzj,
               round(nvl(sum(case
                               when fund_type = 0 then
                                amt3
                             end),
                         0),
                     2) lczj,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt1
                             end),
                         0),
                     2) dfjexj,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt2
                             end),
                         0),
                     2) dfgz,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt3
                             end),
                         0),
                     2) dfylj,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt4
                             end),
                         0),
                     2) dfgk,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt5
                             end),
                         0),
                     2) dfcz,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt6
                             end),
                         0),
                     2) dfbx,
               round(nvl(sum(case
                               when fund_type = 1 then
                                amt7
                             end),
                         0),
                     2) dfyy,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt1
                             end),
                         0),
                     2) dsjexj,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt2
                             end),
                         0),
                     2) sjdj,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt3
                             end),
                         0),
                     2) df,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt4
                             end),
                         0),
                     2) sf,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt5
                             end),
                         0),
                     2) skh,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt6
                             end),
                         0),
                     2) bmyzt,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt7
                             end),
                         0),
                     2) dsjy,
               round(nvl(sum(case
                               when fund_type = 2 then
                                amt8
                             end),
                         0),
                     2) dsbx,
               round(nvl(sum(case
                               when fund_type = 6 then
                                amt1
                             end),
                         0),
                     2) dg,
               round(nvl(sum(case
                               when fund_type = 6 then
                                amt2
                             end),
                         0),
                     2) zhhk,
               round(nvl(sum(case
                               when fund_type = 6 then
                                amt3
                             end),
                         0),
                     2) xdzj,
               round(nvl(sum(case
                               when fund_type = 3 then
                                amt1
                             end),
                         0),
                     2) ks_zcxj,
               round(nvl(sum(case
                               when fund_type = 3 then
                                amt2
                             end),
                         0),
                     2) ks_ydcq,
               round(nvl(sum(case
                               when fund_type = 3 then
                                amt3
                             end),
                         0),
                     2) ks_zz,
               round(nvl(sum(case
                               when fund_type = 3 then
                                amt4
                             end),
                         0),
                     2) ks_syt,
               round(nvl(sum(case
                               when fund_type = 4 then
                                amt1
                             end),
                         0),
                     2) sn_zcxj,
               round(nvl(sum(case
                               when fund_type = 4 then
                                amt2
                             end),
                         0),
                     2) sn_ydcq,
               round(nvl(sum(case
                               when fund_type = 4 then
                                amt3
                             end),
                         0),
                     2) sn_zz,
               round(nvl(sum(case
                               when fund_type = 4 then
                                amt4
                             end),
                         0),
                     2) sn_syt,
               round(nvl(sum(case
                               when fund_type = 6 then
                                amt4
                             end),
                         0),
                     2) bwd_zc,
               round(nvl(sum(case
                               when fund_type = 5 then
                                amt1
                             end),
                         0),
                     2) kh_zcxj,
               round(nvl(sum(case
                               when fund_type = 5 then
                                amt2
                             end),
                         0),
                     2) kh_ydcq,
               round(nvl(sum(case
                               when fund_type = 5 then
                                amt3
                             end),
                         0),
                     2) kh_zz,
               round(nvl(sum(case
                               when fund_type = 5 then
                                amt4
                             end),
                         0),
                     2) kh_syt
          from t_acc_bal_chg_dtl a
         where a.txn_date >= '20110701'
           and a.txn_date <= '20110728'
           and a.inst_no = '350101003'
         group by a.acc) a1,
       (select b.acc,
               b.cust_name,
               b.cust_id,
               b.assets,
               b.vip_stat,
               b.psbc_vip_cd,
               b.cust_manager_id_zz,
               b.cust_manager_name_zz,
               b.cust_manager_id_jz,
               b.cust_manager_name_jz,
               b.cust_manager_id_xd,
               b.cust_manager_name_xd,
               round(nvl(sum(b.tran_amt), 0), 2) bq
          from t_acc_bal_chg_info b
         where b.txn_date >= '20110701'
           and b.txn_date <= '20110728'
         group by b.acc,
                  b.cust_name,
                  b.cust_name,
                  b.cust_id,
                  b.assets,
                  b.vip_stat,
                  b.psbc_vip_cd,
                  b.cust_manager_id_zz,
                  b.cust_manager_name_zz,
                  b.cust_manager_id_jz,
                  b.cust_manager_name_jz,
                  b.cust_manager_id_xd,
                  b.cust_manager_name_xd) b1
 where a1.re_acc = b1.acc(+)
 order by nvl(bq, 0) DESC

Answer 1

执行计划PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1167080402
--------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |     3 |  7950
|   1 |  SORT ORDER BY                  |                       |     3 |  7950
|*  2 |   HASH JOIN OUTER               |                       |     3 |  7950
|   3 |    VIEW                         |                       |     3 |  7392
|   4 |     HASH GROUP BY               |                       |     3 |   192
|   5 |      TABLE ACCESS BY INDEX ROWID| T_ACC_BAL_CHG_DTL     |     6 |   384
|*  6 |       INDEX RANGE SCAN          | IDX_ACC_BAL_CHG_DTL   |     6 |
|   7 |    VIEW                         |                       |     2 |   372
|   8 |     HASH GROUP BY               |                       |     2 |    90
|   9 |      TABLE ACCESS BY INDEX ROWID| T_ACC_BAL_CHG_INFO    |     2 |    90
|* 10 |       INDEX RANGE SCAN          | IDX_ACC_BAL_CHGL_INFO |     2 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - access("A1"."RE_ACC"="B1"."ACC"(+))
   6 - access("A"."TXN_DATE">='20110701' AND "A"."INST_NO"='350101003' AND
              "A"."TXN_DATE"<='20110728')
       filter("A"."INST_NO"='350101003')
  10 - access("B"."TXN_DATE">='20110701' AND "B"."TXN_DATE"<='20110728')
Note
-----
   - 'PLAN_TABLE' is old version表结构create table T_ACC_BAL_CHG_DTL
(
  TXN_DATE    CHAR(8),
  ACC         CHAR(19),
  INST_NO     CHAR(9),
  FUND_NAME1  VARCHAR2(60),
  AMT1        NUMBER(16,2),
  FUND_NAME2  VARCHAR2(60),
  AMT2        NUMBER(16,2),
  FUND_NAME3  VARCHAR2(60),
  AMT3        NUMBER(16,2),
  FUND_NAME4  VARCHAR2(60),
  AMT4        NUMBER(16,2),
  FUND_NAME5  VARCHAR2(60),
  AMT5        NUMBER(16,2),
  FUND_NAME6  VARCHAR2(60),
  AMT6        NUMBER(16,2),
  FUND_NAME7  VARCHAR2(60),
  AMT7        NUMBER(16,2),
  FUND_NAME8  VARCHAR2(60),
  AMT8        NUMBER(16,2),
  FUND_NAME9  VARCHAR2(60),
  AMT9        NUMBER(16,2),
  FUND_NAME10 VARCHAR2(60),
  AMT10       NUMBER(16,2),
  FUND_TYPE   CHAR(1)
);
create table T_ACC_BAL_CHG_INFO
(
  TXN_DATE             CHAR(8),
  ACC                  CHAR(19),
  CUST_NAME            VARCHAR2(40),
  CUST_ID              CHAR(14),
  ASSETS               VARCHAR2(20),
  VIP_STAT             VARCHAR2(20),
  PSBC_VIP_CD          CHAR(20),
  CUST_MANAGER_ID_ZZ   CHAR(9),
  CUST_MANAGER_NAME_ZZ VARCHAR2(20),
  CUST_MANAGER_ID_JZ   CHAR(9),
  CUST_MANAGER_NAME_JZ VARCHAR2(20),
  CUST_MANAGER_ID_XD   CHAR(9),
  CUST_MANAGER_NAME_XD VARCHAR2(20),
  INST_NO              CHAR(9),
  TRAN_AMT             NUMBER(16,2)
);

Answer 2

1、a1子查询语句中把case换成decode2、b1子查询可改写
select b.acc,
       b.cust_name,
       b.cust_id,
       b.assets,
       b.vip_stat,
       b.psbc_vip_cd,
       b.cust_manager_id_zz,
       b.cust_manager_name_zz,
       b.cust_manager_id_jz,
       b.cust_manager_name_jz,
       b.cust_manager_id_xd,
       b.cust_manager_name_xd,
       round(nvl(sum(b.tran_amt)over(), 0), 2) bq
  from t_acc_bal_chg_info b
 where b.txn_date >= '20110701'
   and b.txn_date <= '20110728'

Answer 3

decode与case效率应该差不了多少吧？
第二个那个over()报错了

Answer 4

各位大神，
刚问了下,t_acc_bal_chg_dtl这张表就6千多万的数据，另外张表数据也很大。
直接执行这样的语句，效率很挺快的
select * 
from t_acc_bal_chg_dtl a
         where a.txn_date >= '20110701'
           and a.txn_date <= '20110728'
           and a.inst_no = '350101003'
这样查询出来，就几万的数据，
就是上面执行了分组汇总，还要根据fund_type不同的值行转列，这样效率就很慢了
求助~~~~

Answer 5

--从你的SQL和执行计划看还真想不出优化的地方了。
--建议你如果能够建中间临时表的话，将上下两部分数据建成临时中间表，然后再建立索引，这样效率也许可以快点。--另外a.inst_no 上是否存在索引，可以考虑在上面根据数据情况建立索引=号可以直接定位数据的。

Answer 6

所以是有
TXN_DATE, ACC, FUND_TYPE, INST_NO这几个是联合索引