检查 WHERE 子句中参数是否为 NULL

我在执行一个存储过程时遇到了麻烦，该过程需要永远执行。它相当大，我可以理解我需要一些时间，但这个持续了将近 20 分钟。

经过一些调试和研究后，我注意到替换这部分WHERE clause;

((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与子查询一起使用，但我不明白为什么，因为子查询本身工作得很好。

提前致谢！

UPDATE[2011-09-23 10:13]

我已将问题分解为显示相同行为的小查询；

实施例A

SQL查询

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

SQL查询

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;

我在使用 SQL 语句调优方面也取得了一些成功OR通过使用 UNION ALL 将语句分成两个互斥的语句：

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;