各位高手请帮帮我,下面SQL中--部分用到嵌套IN语句执行效率很慢,请问怎么改成JOIN语句提高效率SELECT EDIH_INV_NO,
EDIH_DDU_DATE,
EDIH_BL_NO
FROM NEXTUSR.ESDIH
WHERE (
(EDIH_INV_MODE = 'S')
--
OR (EDIH_INV_NO IN (SELECT IIVH_EINVNO
FROM NEXTUSR.ITIVH
WHERE IIVH_INVNUM IN (SELECT IIVD_INVNUM FROM NEXTUSR.ITIVD WHERE IIVD_IRIKBN = '42'
)
)
)
--
)
AND EDIH_FXED071 IS NULL
AND EDIH_AKAKURO_KBN = '1'
AND LENGTH(EDIH_INV_NO) < 9
ORDER BY EDIH_INV_NO;
EDIH_DDU_DATE,
EDIH_BL_NO
FROM NEXTUSR.ESDIH
WHERE (
(EDIH_INV_MODE = 'S')
--
OR (EDIH_INV_NO IN (SELECT IIVH_EINVNO
FROM NEXTUSR.ITIVH
WHERE IIVH_INVNUM IN (SELECT IIVD_INVNUM FROM NEXTUSR.ITIVD WHERE IIVD_IRIKBN = '42'
)
)
)
--
)
AND EDIH_FXED071 IS NULL
AND EDIH_AKAKURO_KBN = '1'
AND LENGTH(EDIH_INV_NO) < 9
ORDER BY EDIH_INV_NO;
解决方案 »
- oracle 返回主键的问题
- 与表关联进行行转列
- 请问哪位大虾,如下的语句如何改写才能避免四层嵌套循环(用explain plan 分析时所的结果)?分不够再加。
- oracle客户端与服务器端字符集不一致,修改客户端,服务器启动失败
- 如何用JDBC从Oracle中获取max_idle_time
- 如何将多个日期转化成数值后求平均值
- 如何远程调用存储过程?
- svrmgrm在sco unix 5.06的使用?急急急!!!
- 问一个很基础的问题
- 江湖救急啊~~ORACLE的BDE安装打包问题
- 如何对数据库中的某个字段进行加密(插入时加密,提取时解密)
- shell脚本获取sqlplus错误码ORA-00020问题
SELECT EDIH_INV_NO,
EDIH_DDU_DATE,
EDIH_BL_NO
FROM NEXTUSR.ESDIH
WHERE EDIH_INV_MODE = 'S'
AND EDIH_FXED071 IS NULL
AND EDIH_AKAKURO_KBN = '1'
AND LENGTH(EDIH_INV_NO) < 9
UNION
SELECT EDIH_INV_NO,
EDIH_DDU_DATE,
EDIH_BL_NO
FROM NEXTUSR.ESDIH
WHERE
EDIH_INV_NO IN (SELECT IIVH_EINVNO
FROM NEXTUSR.ITIVH
WHERE IIVH_INVNUM IN (SELECT IIVD_INVNUM FROM NEXTUSR.ITIVD WHERE IIVD_IRIKBN = '42' ))
AND EDIH_FXED071 IS NULL
AND EDIH_AKAKURO_KBN = '1'
AND LENGTH(EDIH_INV_NO) < 9
ORDER BY EDIH_INV_NO;
SELECT EDIH_INV_NO,
EDIH_DDU_DATE,
EDIH_BL_NO
FROM NEXTUSR.ESDIH a
WHERE (
(EDIH_INV_MODE = 'S')
--
OR ( EXISTS (SELECT 1
FROM NEXTUSR.ITIVH b
WHERE a.EDIH_INV_NO=b.IIVH_EINVNO
AND EXISTS (SELECT 1 FROM NEXTUSR.ITIVD WHERE IIVD_IRIKBN = '42' AND b.IIVH_INVNUM=IIVD_INVNUM)
)
)
--
)
AND EDIH_FXED071 IS NULL
AND EDIH_AKAKURO_KBN = '1'
AND LENGTH(EDIH_INV_NO) < 9
ORDER BY EDIH_INV_NO;
原来那个SQL一执行就要死机。。
真的太谢谢了
能告诉一下原因就太好了
--修改两点:
--1、先过滤确定条件
--2、in 改成exists
--其它我没想法,你试试
SELECT a.EDIH_INV_NO,
a.EDIH_DDU_DATE,
a.EDIH_BL_NO
FROM NEXTUSR.ESDIH a
WHERE a.EDIH_FXED071 IS NULL
AND a.EDIH_AKAKURO_KBN = '1'
AND LENGTH(a.EDIH_INV_NO) < 9
AND (
(a.EDIH_INV_MODE = 'S')
--
OR ( EXISTS (SELECT 1 FROM NEXTUSR.ITIVH b
WHERE a.EDIH_INV_NO=b.IIVH_EINVNO
AND EXISTS (SELECT 1 FROM NEXTUSR.ITIVD WHERE IIVD_IRIKBN = '42' AND b.IIVH_INVNUM=IIVD_INVNUM)
)
)
--
)
ORDER BY a.EDIH_INV_NO;
UNION
SELECT a.EDIH_INV_NO,a.EDIH_DDU_DATE,a.EDIH_BL_NO FROM NEXTUSR.ESDIH a,NEXTUSR.ITIVH b,NEXTUSR.ITIVD c WHERE a.EDIH_INV_NO=b.IIVH_EINVNO and b.IIVH_EINVNO=c.IIVD_INVNUM
AND c.IIVD_IRIKBN='42' AND a.EDIH_AKAKURO_KBN = '1' AND a.LENGTH(EDIH_INV_NO) < 9 ORDER BY a.EDIH_INV_NO;
SELECT EDIH_INV_NO,
EDIH_DDU_DATE,
EDIH_BL_NO
FROM NEXTUSR.ESDIH a
WHERE EDIH_FXED071 IS NULL
AND EDIH_AKAKURO_KBN = '1'
AND LENGTH(EDIH_INV_NO) < 9 AND
(
(EDIH_INV_MODE = 'S')
OR exists(SELECT 1
FROM NEXTUSR.ITIVH b
WHERE a.EDIH_INV_NO=b.IIVH_EINVNO
and exists(SELECT 1 FROM NEXTUSR.ITIVD c WHERE IIVD_IRIKBN = '42' and b.IIVD_INVNUM=c.IIVD_INVNUM)
)
) )
ORDER BY EDIH_INV_NO;
总结了一下问题出在WHERE语句里面
把原来的SQL拆成2句SQL再用UNION连接效率果然快了,只要不到10秒。2楼和6楼的也谢谢了,
IN改EXISTS之后效果不是很明显,
还是会卡在那里的