一个通过dblink查询的优化案例（去掉filter）

最新推荐文章于 2024-04-29 14:21:15 发布

jgmydsai

最新推荐文章于 2024-04-29 14:21:15 发布

阅读量3k

点赞数

分类专栏：改写优化

本文链接：https://blog.csdn.net/jgmydsai/article/details/16827003

版权

改写优化专栏收录该内容

30 篇文章 4 订阅

订阅专栏

原语句如下

EXPLAIN PLAN  FOR SELECT a.*, b.rkids
  FROM (SELECT gys.khbh,
               gys.khmc,
               wz.wzzbm,
               wz.wzmbm,
               wz.wzmc,
               wz.wzgg,
               a.sl,
               wz.jldw,
               wz.wzflmbm,
               a.sl * wz.wzflmbm jhje
          FROM wz@dblink wz,
               gys@dblink gys,
               (SELECT m.khbh,
                       d.wzzbm,
                       SUM(CASE m.rkzt
                             WHEN '2' THEN
                              d.xysl
                             ELSE
                              -1 * d.xysl
                           END) sl
                  FROM m@dblink m, d@dblink d
                 WHERE m.rkid = d.rkid
                   AND m.rkzt IN (2, 3)
                   AND m.ssny < '201311'
                 GROUP BY m.khbh, d.wzzbm) a
         WHERE a.sl > 0
           AND gys.khbh = a.khbh
           AND wz.wzzbm = a.wzzbm/*
           and gys.khbh='00342'
              and wz.wzzbm='07524'*/) a,
       (SELECT m.khbh, d.wzzbm, wmsys.wm_concat(m.rkid) rkids
          FROM m@dblink m, d@dblink d
         WHERE m.rkid = d.rkid/*
              and m.khbh='00342'
              and d.wzzbm='07524'*/
           AND m.rkzt = 2
           AND m.ssny < '201311'
           AND m.zxdid IS NULL
           AND (NOT EXISTS (SELECT 1
                              FROM m@dblink m1, d@dblink d1
                             WHERE m1.rkid = d1.rkid
                               AND m1.zxdid = m.rkid
                               AND d1.wzzbm = d.wzzbm
                               AND m1.rkzt = 3) OR
                (SELECT SUM(d1.xysl)
                              FROM m@dblink m1, d@dblink d1
                             WHERE m1.rkid = d1.rkid
                               AND m1.zxdid = m.rkid
                               AND d1.wzzbm = d.wzzbm
                               AND m1.rkzt = 3) < d.xysl)
         GROUP BY m.khbh, d.wzzbm) b
 WHERE a.khbh = b.khbh(+)
   AND a.wzzbm = b.wzzbm(+);

1	Plan hash value: 2646510213
2	 
3	---------------------------------------------------------------------------------------------------
4	| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
5	---------------------------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT      |           |   702K|  1445M|   236  (15)| 00:00:03 |        |      |
7	|*  1 |  HASH JOIN RIGHT OUTER|           |   702K|  1445M|   236  (15)| 00:00:03 |        |      |
8	|   2 |   VIEW                |           |     1 |  2018 |   128   (8)| 00:00:02 |        |      |
9	|   3 |    SORT GROUP BY      |           |     1 |    67 |   128   (8)| 00:00:02 |        |      |
10	|*  4 |     FILTER            |           |       |       |            |          |        |      |
11	|   5 |      REMOTE           |           |     1 |    38 |   124   (7)| 00:00:02 |     WN | R->S |
12	|   6 |      NESTED LOOPS     |           |     2 |    84 |    12   (0)| 00:00:01 |        |      |
13	|   7 |       REMOTE          | MP_RKDJWZ |     8 |   128 |     4   (0)| 00:00:01 |     WN | R->S |
14	|   8 |       REMOTE          | MP_RKDJ   |     1 |    26 |     1   (0)| 00:00:01 |     WN | R->S |
15	|   9 |        SORT AGGREGATE |           |     1 |    55 |            |          |        |      |
16	|  10 |         NESTED LOOPS  |           |     2 |   110 |    12   (0)| 00:00:01 |        |      |
17	|  11 |          REMOTE       | MP_RKDJWZ |     8 |   232 |     4   (0)| 00:00:01 |     WN | R->S |
18	|  12 |          REMOTE       | MP_RKDJ   |     1 |    26 |     1   (0)| 00:00:01 |     WN | R->S |
19	|  13 |   VIEW                |           |   702K|    93M|    97  (16)| 00:00:02 |        |      |
20	|  14 |    REMOTE             |           |       |       |            |          |     WN | R->S |
21	---------------------------------------------------------------------------------------------------
22	 
23	Predicate Information (identified by operation id):
24	---------------------------------------------------
25	 
26	   1 - access("A"."KHBH"="B"."KHBH"(+) AND "A"."WZZBM"="B"."WZZBM"(+))
27	   4 - filter( NOT EXISTS (SELECT 0 FROM  "A1", "A2" WHERE "M1"."ZXDID"=:B1 AND 
28	              TO_NUMBER("M1"."RKZT")=3 AND "M1"."RKID"="D1"."RKID" AND "D1"."WZZBM"=:B2) OR "D"."XYSL"> 
29	              (SELECT SUM("D1"."XYSL") FROM  "A1", "A2" WHERE "M1"."ZXDID"=:B3 AND 
30	              TO_NUMBER("M1"."RKZT")=3 AND "M1"."RKID"="D1"."RKID" AND "D1"."WZZBM"=:B4))
31	 
32	Remote SQL Information (identified by operation id):
33	----------------------------------------------------
34	 
35	   5 - SELECT "A1"."RKID","A1"."RKID","A1"."RKID","A1"."RKID","A1"."RKZT","A1"."SSNY","A1".
36	       "ZXDID","A1"."KHBH","A2"."WZZBM","A2"."WZZBM","A2"."RKID","A2"."XYSL","A2"."WZZBM" FROM 
37	       "MP_RKDJ" "A1","MP_RKDJWZ" "A2" WHERE "A1"."RKID"="A2"."RKID" AND "A1"."ZXDID" IS NULL AND 
38	       TO_NUMBER("A1"."RKZT")=2 AND "A1"."SSNY"<'201311' (accessing 'WN' )
39	 
40	   7 - SELECT "RKID","WZZBM" FROM "MP_RKDJWZ" "A1" WHERE "WZZBM"=:1 (accessing 'WN' )
41	 
42	   8 - SELECT "RKID","RKZT","ZXDID" FROM "MP_RKDJ" "A2" WHERE "ZXDID"=:1 AND 
43	       TO_NUMBER("RKZT")=3 AND "RKID"=:2 (accessing 'WN' )
44	 
45	  11 - SELECT "RKID","WZZBM","XYSL" FROM "MP_RKDJWZ" "A1" WHERE "WZZBM"=:1 (accessing 'WN' 
46	        )
47	 
48	  12 - SELECT "RKID","RKZT","ZXDID" FROM "MP_RKDJ" "A2" WHERE "ZXDID"=:1 AND 
49	        TO_NUMBER("RKZT")=3 AND "RKID"=:2 (accessing 'WN' )
50	 
51	  14 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT 
52	        "A2"."KHBH","A2"."KHMC","A3"."WZZBM","A3"."WZMBM","A3"."WZMC","A3"."WZGG","A1"."SL","A3"."J
53	        LDW","A3"."WZFLMBM","A1"."SL"*TO_NUMBER("A3"."WZFLMBM") FROM "MP_WZBM" "A3","MP_GYCJDA" 
54	        "A2", (SELECT "A5"."KHBH" "KHBH","A4"."WZZBM" "WZZBM",SUM(CASE "A5"."RKZT" WHEN '2' THEN 
55	        "A4"."XYSL" ELSE (-1)*"A4"."XYSL" END ) "SL" FROM "MP_RKDJ" "A5","MP_RKDJWZ" "A4" WHERE 
56	        "A5"."RKID"="A4"."RKID" AND (TO_NUMBER("A5"."RKZT")=2 OR TO_NUMBER("A5"."RKZT")=3) AND 
57	        "A5"."SSNY"<'201311' GROUP BY "A5"."KHBH","A4"."WZZBM" HAVING SUM(CASE "A5"."RKZT" WHEN 
58	        '2' THEN "A4"."XYSL" ELSE (-1)*"A4"."XYSL" END )>0) "A1" WHERE "A2"."KHBH"="A1"."KHBH" AND 
59	        "A3"."WZZBM"="A1"."WZZBM" (accessing 'WN' )

语句中远端表分别走了nl与filter，这样肯定很慢，而且查询中对表有好几个不必要的重复访问，于是更改如下

SELECT gys.khbh,
       gys.khmc,
       wz.wzzbm,
       wz.wzmbm,
       wz.wzmc,
       wz.wzgg,
       a.sl,
       wz.jldw,
       wz.wzflmbm,
       a.sl * wz.wzflmbm jhje,
       a.rkids
  FROM mp_wzbm@wn wz,
       mp_gycjda@wn gys,
       (SELECT m.khbh,
               d.wzzbm,
               SUM(CASE m.rkzt
                     WHEN '2' THEN
                      d.xysl
                     ELSE
                      -1 * d.xysl
                   END) sl,
               wmsys.wm_concat(CASE
                                 WHEN (m1.zxdid IS NULL OR nvl(m1.xysl, 0) < d.xysl) THEN
                                  m.rkid
                               END) AS rkids
          FROM mp_rkdj@wn m
         INNER JOIN mp_rkdjwz@wn d
            ON (m.rkid = d.rkid)
          LEFT JOIN (SELECT SUM(d1.xysl) AS xysl, m1.zxdid, d1.wzzbm
                      FROM mp_rkdj@wn m1, mp_rkdjwz@wn d1
                     WHERE m1.rkid = d1.rkid
                       AND m1.rkzt = 3
                     GROUP BY m1.zxdid, d1.wzzbm) m1
            ON (m1.zxdid = = m.rkid AND m1.wzzbm = d.wzzbm)
         WHERE m.rkzt IN (2, 3)
           AND m.ssny < '201311'
         GROUP BY m.khbh, d.wzzbm) a
 WHERE a.sl > 0
   AND gys.khbh = a.khbh
   AND wz.wzzbm = a.wzzbm

改后的语句直接走了hash，这也好，省的加hint了。

jgmydsai

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一个通过dblink查询的优化案例（去掉filter）

原语句如下EXPLAIN PLAN FOR SELECT a.*, b.rkids FROM (SELECT gys.khbh, gys.khmc, wz.wzzbm, wz.wzmbm, wz.wzmc, wz.wzgg,
复制链接

扫一扫