没辙了 看看这sql 怎么优化 给个意见

Question

SELECT   ck_wzmx.cghthm, ck_wzmx.cz, ck_wzmx.pm, ck_wzmx.gg, ck_wzmx.cd,
         ck_wzmx.jldw2, ck_wzmx.jldw1, ck_wzmx.wzdm, ck_wzmx.sl1, ck_wzmx.sl2,
         SUM (NVL (v_ck_wzmxdhl.sl1, 0)), SUM (NVL (v_ck_wzmxdhl.sl2, 0)),
           ck_wzmx.sl1
         - NVL (SUM (NVL (v_ck_wzmxdhl.sl1, 0)), 0)
         - NVL (SUM (NVL (v_ck_wzmxdhl.jgsl1, 0)), 0)
         - NVL (SUM (NVL (v_ck_wzmxdhl.sgsdsl1, 0)), 0),
           ck_wzmx.sl2
         - NVL (SUM (NVL (v_ck_wzmxdhl.sl2, 0)), 0)
         - NVL (SUM (NVL (v_ck_wzmxdhl.jgsl2, 0)), 0)
         - NVL (SUM (NVL (v_ck_wzmxdhl.sgsdsl2, 0)), 0),
         ck_wzmx.sl1 - SUM (NVL (v_ck_wzmxdhl.htsl1, 0)), ck_wzmx.ck,
         ck_wzmx.sl2 - SUM (NVL (v_ck_wzmxdhl.htsl2, 0)), ck_wzmx.bz,
         ck_wzmx.bmmc, ck_wzmx.fgs, ck_wzmx.syr, ck_wzmx.hz, ck_wzmx.ywy,
         ck_wzmx.jlfs, 0, 0, ck_wzmx.xsdj, SUM (NVL (v_ck_wzmxdhl.jgsl1, 0)),
         SUM (NVL (v_ck_wzmxdhl.jgsl2, 0)), ck_wzmx.lsxs, ck_wzmx.string1,
         ck_wzmx.string2, ck_wzmx.string3, ck_wzmx.string4, ck_wzmx.string5,
         ck_wzmx.string6, ck_wzmx.string7, ck_wzmx.string8, ck_wzmx.numeric1,
         ck_wzmx.numeric2, ck_wzmx.numeric3, ck_wzmx.datetime1,
         ck_wzmx.datetime2, ck_wzmx.datetime3, ck_wzmx.wzstr1, ck_wzmx.wzstr2,
         ck_wzmx.wzstr3, ck_wzmx.wznum1, ck_wzmx.wznum2, ck_wzmx.cghtstr1,
         ck_wzmx.cghtstr2, ck_wzmx.cghtstr3, ck_wzmx.rkph, ck_wzmx.rkphstr1,
         ck_wzmx.rkphstr2, ck_wzmx.rkphstr3, ck_wzmx.rkmdh, ck_wzmx.rkmdstr1,
         ck_wzmx.rkmdstr2, ck_wzmx.rkmdstr3, ck_wzmx.rkmdstr4,
         ck_wzmx.rkmdstr5, ck_wzmx.rkmdnum1, ck_wzmx.rkmdnum2,
         ck_wzmx.rkmdnum3, ck_wzmx.cghtph, '', NVL (ck_wzmx.rq, SYSDATE),
         ck_wzmx.kh, 0, TRUNC (SYSDATE - ck_wzmx.rq), ck_wzmx.wzsl1,
         ck_wzmx.wzsl2, ck_wzmx.yssl1, ck_wzmx.yssl2,
           ck_wzmx.wzsl1
         - NVL (SUM (NVL (v_ck_wzmxdhl.wzsl1, 0)), 0)
         - NVL (SUM (NVL (v_ck_wzmxdhl.jgsl1, 0)), 0),
           ck_wzmx.wzsl2
         - SUM (NVL (v_ck_wzmxdhl.wzsl2, 0))
         - SUM (NVL (v_ck_wzmxdhl.jgsl2, 0)),
         ck_wzmx.wzsl1 - SUM (NVL (v_ck_wzmxdhl.htwzsl1, 0)),
         ck_wzmx.wzsl2 - SUM (NVL (v_ck_wzmxdhl.htwzsl2, 0)), NULL,
         bd_ckkw.kwmc, bd_ck.mc, SUM (NVL (v_ck_wzmxdhl.sgsdsl1, 0)),
         SUM (NVL (v_ck_wzmxdhl.sgsdsl2, 0)), ck_wzmx.jgxz, ck_wzmx.ywlb,
         ck_wzmx.xz, ck_wzmx.ywlx, ck_wzmx.ywfphm, ck_wzmx.kcstr1,
         ck_wzmx.kcstr2, SUM (NVL (v_ck_wzmxdhl.jyzysl1, 0)),
         SUM (NVL (v_ck_wzmxdhl.jyzysl2, 0)), ck_wzmx.rkmdstr6,
         ck_wzmx.mdjbhm, ck_wzmx.rkmdstr7, ck_wzmx.rkmdstr8, ck_wzmx.rkmdstra,
         ck_wzmx.rkmdstrb, ck_wzmx.rkmdstrc, ck_wzmx.rkmdstrd, ck_wzmx.ysrkrq,
         ck_wzmx.wzscrq, TRUNC (SYSDATE - ck_wzmx.ysrkrq), ck_wzmx.rkmdnum4,
         ck_wzmx.rkmdnum5, ck_wzmx.rkmddt1, ck_wzmx.rkmddt2, ck_wzmx.rkmddt3,
         ck_wzmx.rkmddt4, ck_wzmx.rkmddta, ck_wzmx.rkmddtb, ck_wzmx.lhy,
         MAX (NVL (v_ck_wzmxdhl.zt, '')), '', ck_wzmx.hw
    FROM ck_wzmx, v_ck_wzmxdhl, bd_ckkw, bd_ck
   WHERE ck_wzmx.pm = v_ck_wzmxdhl.pm(+)
     AND ck_wzmx.cz = v_ck_wzmxdhl.cz(+)
     AND ck_wzmx.gg = v_ck_wzmxdhl.gg(+)
     AND ck_wzmx.cd = v_ck_wzmxdhl.cd(+)
     AND ck_wzmx.cghtstr1 = v_ck_wzmxdhl.cghtstr1(+)
     AND NVL (ck_wzmx.rkmdstr4, ' ') = NVL (v_ck_wzmxdhl.rkmdstr4(+), ' ')
     AND ck_wzmx.rkmdh = v_ck_wzmxdhl.rkmdh(+)
     AND ck_wzmx.kh = v_ck_wzmxdhl.kh(+)
     AND ck_wzmx.hz = v_ck_wzmxdhl.hz(+)
     AND ck_wzmx.ck = v_ck_wzmxdhl.ck(+)
     AND ck_wzmx.kh = bd_ckkw.kwdm            --and ck_wzmx.ck = bd_ckkw.ckdm)
     AND ck_wzmx.ck = bd_ck.dm
GROUP BY ck_wzmx.cghthm,
         ck_wzmx.cz,
         ck_wzmx.pm,
         ck_wzmx.gg,
         ck_wzmx.cd,
         ck_wzmx.jldw2,
         ck_wzmx.jldw1,
         ck_wzmx.wzdm,
         ck_wzmx.sl1,
         ck_wzmx.sl2,
         ck_wzmx.wzsl1,
         ck_wzmx.wzsl2,
         ck_wzmx.yssl1,
         ck_wzmx.yssl2,
         ck_wzmx.ck,
         ck_wzmx.bz,
         ck_wzmx.bmmc,
         ck_wzmx.fgs,
         ck_wzmx.syr,
         ck_wzmx.hz,
         ck_wzmx.ywy,
         ck_wzmx.jlfs,
         ck_wzmx.wsdj,
         ck_wzmx.dj,
         ck_wzmx.xsdj,
         ck_wzmx.lsxs,
         ck_wzmx.string1,
         ck_wzmx.string2,
         ck_wzmx.string3,
         ck_wzmx.string4,
         ck_wzmx.string5,
         ck_wzmx.string6,
         ck_wzmx.string7,
         ck_wzmx.string8,
         ck_wzmx.numeric1,
         ck_wzmx.numeric2,
         ck_wzmx.numeric3,
         ck_wzmx.datetime1,
         ck_wzmx.datetime2,
         ck_wzmx.datetime3,
         ck_wzmx.wzstr1,
         ck_wzmx.wzstr2,
         ck_wzmx.wzstr3,
         ck_wzmx.wznum1,
         ck_wzmx.wznum2,
         ck_wzmx.cghtstr1,
         ck_wzmx.cghtstr2,
         ck_wzmx.cghtstr3,
         ck_wzmx.rkph,
         ck_wzmx.rkphstr1,
         ck_wzmx.rkphstr2,
         ck_wzmx.rkphstr3,
         ck_wzmx.rkmdh,
         ck_wzmx.rkmdstr1,
         ck_wzmx.rkmdstr2,
         ck_wzmx.rkmdstr3,
         ck_wzmx.rkmdstr4,
         ck_wzmx.rkmdstr5,
         ck_wzmx.rkmdnum1,
         ck_wzmx.rkmdnum2,
         ck_wzmx.rkmdnum3,
         ck_wzmx.cghtph,
         ck_wzmx.xshtph,
         ck_wzmx.rq,
         ck_wzmx.kh,
         TRUNC (SYSDATE - ck_wzmx.rq),
         bd_ckkw.kwmc,
         bd_ck.mc,
         ck_wzmx.jgxz,
         ck_wzmx.ywlb,
         ck_wzmx.xz,
         ck_wzmx.ywlx,
         ck_wzmx.ywfphm,
         ck_wzmx.kcstr1,
         ck_wzmx.kcstr2,
         ck_wzmx.rkmdstr6,
         ck_wzmx.mdjbhm,
         ck_wzmx.rkmdstr7,
         ck_wzmx.rkmdstr8,
         ck_wzmx.rkmdstra,
         ck_wzmx.rkmdstrb,
         ck_wzmx.rkmdstrc,
         ck_wzmx.rkmdstrd,
         ck_wzmx.ysrkrq,
         ck_wzmx.wzscrq,
         TRUNC (SYSDATE - ck_wzmx.ysrkrq),
         ck_wzmx.rkmdnum4,
         ck_wzmx.rkmdnum5,
         ck_wzmx.rkmddt1,
         ck_wzmx.rkmddt2,
         ck_wzmx.rkmddt3,
         ck_wzmx.rkmddt4,
         ck_wzmx.rkmddta,
         ck_wzmx.rkmddtb,
         ck_wzmx.lhy,
         ck_wzmx.hwck_wzmx 有近40万条,目前查询速度1分钟左右
怎么办? 所有的where 的都加了索引了 还是慢
想把表放到内存里 不知是否可行
或者键分区表

Answer 1

要写出这样的SQL，说明表结果设计有问题

Answer 2

补充:v_ck_wzmxdhl 也是个大视图
1 row selected.Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer Mode=ALL_ROWS   1     57501                           
   1    0   SORT AGGREGATE   1                               
   2    1     VIEW aa.V_CK_FORSHYX 312 K   57501                           
   3    2       SORT GROUP BY   312 K 28 M 57501                           
   4    3         VIEW aa.V_CK_WZMXB 312 K 28 M 50557                           
   5    4           SORT GROUP BY   312 K 212 M 50557                           
   6    5             HASH JOIN RIGHT OUTER   312 K 212 M 3473                           
   7    6               VIEW aa.V_CK_WZMXDHL 1 K 437 K 188                           
   8    7                 UNION-ALL                                  
   9    8                   SORT GROUP BY   1 K 423 K 132                           
  10    9                     HASH JOIN   1 K 423 K 37                           
  11   10                       TABLE ACCESS FULL aa.AA_CKD 6 K 132 K 21                           
  12   10                       TABLE ACCESS FULL aa.AA_CKD_MA 1 K 400 K 15                           
  13    8                   SORT GROUP BY   46   15 K 12                           
  14   13                     HASH JOIN   46   15 K 11                           
  15   14                       TABLE ACCESS FULL aa.CK_SHD 46   1 K 2                           
  16   14                       TABLE ACCESS FULL aa.CK_SHD_MD 1 K 330 K 8                           
  17    8                   HASH JOIN ANTI   2   458   5                           
  18   17                     TABLE ACCESS FULL aa.CK_ZYD_MD 3   564   2                           
  19   17                     TABLE ACCESS FULL aa.CK_JYD_MD 4   164   2                           
  20    8                   HASH JOIN   46   13 K 7                           
  21   20                     TABLE ACCESS FULL aa.CK_WZJGDM 65   1 K 3                           
  22   20                     TABLE ACCESS FULL aa.CK_WZJGD 46   12 K 3                           
  23    8                   TABLE ACCESS BY INDEX ROWID aa.CK_GHD 1   32   1                           
  24   23                     NESTED LOOPS   1   227   30                           
  25   24                       TABLE ACCESS FULL aa.CK_GHD_A 28   5 K 2                           
  26   24                       INDEX RANGE SCAN aa.CK_GHD_INDEX 1     1                           
  27    8                   SORT GROUP BY   32   11 K 3                           
  28   27                     TABLE ACCESS FULL aa.CK_SGSD_MD 32   11 K 2                           
  29    6               HASH JOIN   312 K 101 M 3282                           
  30   29                 MERGE JOIN CARTESIAN   372   9 K 5                           
  31   30                   TABLE ACCESS FULL aa.BD_CK 6   78   2                           
  32   30                   BUFFER SORT   62   806   3                           
  33   32                     TABLE ACCESS FULL aa.BD_CKKW 62   806   1                           
  34   29                 TABLE ACCESS FULL aa.CK_WZMX 312 K 93 M 3274                           Statistics
----------------------------------------------------------
        197  recursive calls
          0  global enqueue CPU used by this session
         49  db block gets
         49  db block gets from cache
          0  calls to kcmgcs
        607  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  bytes sent via SQL*Net to dblink
          2  sorts (disk)
     622024  sorts (rows)
          1  rows processed

Answer 3

用plsql看执行计划，先确定哪段有问题，执行时间最长。

Answer 4

你索引怎么建的？统计信息是最新的吗？怎么全是TABLE ACCESS FULL ？
返回的数据量占全表多大比例呢？

Answer 5

只返回一条记录？那就想办法走索引。
还有622024  sorts (rows) 太高了，看有没有可以不排序的。

Answer 6

详细分析视图，使其整个sql中的内容全选自表(table而不是view)。如此一来，有两点好处：
1.省去了中转（UGA运算和存放view的空间）；
2.可以象吃鱼一样避开一根一根的鱼刺，寻找有优化的地方。

Answer 7

看看group by的部分顺序有没有优化的地方(尽量和where中关联条件的顺序一致),不要建过包含过多列的索引(也不要建立过多索引),按照列值选择度排序,选择不超过10个列进行索引.
表设计看起来是相当有问题的,即使全部放入内存,临时表空间也是相当可观的.
视图进行分解,将和此查询无关的基表从查询中去除.

Answer 8

建物化视图然后数据库闲时用JOB定点执行。

Answer 9

......
  FROM ck_wzmx a, v_ck_wzmxdhl b, bd_ckkw c, bd_ck d
 WHERE a.pm = b.pm(+)
   AND a.cz = b.cz(+)
   AND a.gg = b.gg(+)
   AND a.cd = b.cd(+)
   AND a.cghtstr1 = b.cghtstr1(+)
   AND NVL(a.rkmdstr4, ' ') = NVL(b.rkmdstr4(+), ' ')
   AND a.rkmdh = b.rkmdh(+)
   AND a.kh = b.kh(+)
   AND a.hz = b.hz(+)
   AND a.ck = b.ck(+)
   AND a.kh = c.kwdm 
   AND a.ck = d.dm
 GROUP BY a.cghthm,......--从查询条件来看，a表与b表(视图)关联较多，
--也许视图中的好多字段取自于a,c,d表。
--所以要注意优化b表创建时  与  此sql中的b和a,c,d表中相重复关联的部分。

Answer 10

太复杂的逻辑了, 任何优化都起不到根本作用建议放web服务器里内存扫描, 很快的
上个月我就做了一个, 31ms