oracle – 搜查WHERE子句中的参数是否为NULL

我碰着了一个必要永世执行的存储进程.这长短常大的,我可以领略,我会花一些时刻,但这一次一连快要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

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)举办全表扫描.关于怎样停止这种环境的任何设法？

办理要领

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;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!