一个通过dblink查询的优化案例（去掉filter）
来源：互联网 发布：淘宝影响实体店 编辑：程序博客网 时间：2024/06/03 13:26
原语句如下
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(+);
1Plan hash value: 26465102132 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 23Predicate 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 32Remote 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"."J53 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' )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了。
- 一个通过dblink查询的优化案例（去掉filter）
- 创建一个指向本地数据库的 dblink,并通过 dblink 查询一个表中的数据
- 一个查询（报表）优化案例
- 经典案例：如何优化Oracle使用DBlink的SQL语句
- 一个跨库复杂查询的SQL优化的案例
- 优化案例1---用分析函数优化优化执行计划中的FILTER以及标量子查询
- 一个sql优化的案例
- 通过存储过程执行通过DBLINK的查询语句失败-单个语句成功--ORA-00604
- dblink的查询与删除
- 关于dblink的sql优化
- mysql 查询优化案例
- 本地应用通过Dblink查询，导致Weblogic的线程堵塞而垮掉
- MySQL优化案例---半连接（semi join）优化方式 导致的查询性能低下
- 帮盖尔优化SQL-----子查询优化的经典案例
- oracle :性能优化的一个案例
- oracle :性能优化的一个案例
- MySQL NOT EXISTS优化的一个案例
- MySQL NOT EXISTS优化的一个案例
- POJ2991:Crane(线段树+几何)
- Am335x 下GPIO控制实例
- 我的Android进阶之旅------>Android中MediaButtonReceiver广播监听器的机制分析
- c语言 经典累加算法
- 7种形式的Android Dialog使用举例
- 一个通过dblink查询的优化案例（去掉filter）
- demo制作
- Delphi SelectSingleNode的使用 根据节点属性获取该节点
- (Relax 快速幂取模1.3)POJ 1995 Raising Modulo Numbers(快速幂取模模板题+同余模公式)
- installshield 2009 无法创建快捷方式
- 你的应用如何成为分享应用程序（share content） 或者 可以增加账户（add an account app list）的应用程序
- 桶排序算法
- vsftpd 打开日志输出功能
- IOS开发学习基础--------C语言(一)
