我遇到了一个需要永久执行的存储过程.这是非常大的,我可以理解,我会花一些时间,但这一次持续将近20分钟.
经过一些调试和研究后,我注意到替换了WHERE子句的这一部分;
((p_DrumNo IS NULL) OR T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))
发了很大的变化.所以只要p_DrumNo为NULL,或者我修改上面的内容就不检查p_DrumNo是否为NULL,程序就可以正常工作;
(T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))
此WHERE子句的目标是在p_DrumNo上传递结果集,如果它传入存储过程.然后WHERE子句继续进一步的条件,但这个特定的条件停止查询.
ORDERDELIVERY只是一个包含与参数p_DrumNo相关的ORDER_ID的临时表.
这个简单的IS NULL检查怎么会造成如此大的影响呢？它可能与OR与子查询一起使用有关,但我不明白为什么子查询本身工作得很好.
提前致谢！
更新[2011-09-23 10:13]
例A
SELECT * FROM T_ORDER WHERE
('290427' IS NULL OR ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%') );
执行计划
OPERATION OBJECT_NAME OPTIONS COST ------------------------------------------------------------ SELECT STATEMENT 97 FILTER TABLE ACCESS T_ORDER FULL 95 TABLE ACCESS T_ORDER BY INDEX ROWID 2 INDEX PK_ORDER UNIQUE SCAN 1
例B
SELECT * FROM T_ORDER WHERE ( ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%') );
执行计划
OPERATION OBJECT_NAME OPTIONS COST ------------------------------------------------------------ SELECT STATEMENT 4 NESTED LOOPS 4 TABLE ACCESS T_ORDER BY INDEX ROWID 3 INDEX IX_T_ORDER_ORDERNO RANGE SCAN 2 TABLE ACCESS T_ORDER BY INDEX ROWID 1 INDEX PK_ORDER UNIQUE SCAN 0
大家可以看到第一个查询(例子A)进行全表扫描.关于如何避免这种情况的任何想法？
解决方法
不是在sql语句本身中评估过程的参数状态,而是将该评估移动到包含的PL / sql块,以便在提交理想的sql语句之前只执行一次.例如：
CREATE OR REPLACE PROCEDURE my_sp (p_DrumNo VARCHAR2)
IS
BEGIN
IF p_DrumNo IS NULL THEN
SELECT ...
INTO ... -- Assumed
FROM ...
WHERE my_column = p_DrumNo;
ELSE
SELECT ...
INTO ... -- Assumed
FROM ...
WHERE ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY);
END;
END;
通过使用UNION ALL将语句分成两个互斥语句,我在使用OR调优sql语句方面也取得了一些成功：
SELECT ... FROM ... WHERE p_DrumNo IS NULL AND ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY) UNION ALL SELECT ... FROM ... WHERE p_DrumNo IS NOT NULL AND my_column = p_DrumNo;
