oracle 检查 WHERE 子句中的参数是否为 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7517018/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if parameter is NULL within WHERE clause
提问by Stefan
I′m having trouble with a Stored Procedure that takes about forever to execute. It is quite large and I can understand that I′ll take some time but this one continues for almost 20 minutes.
我遇到了一个需要永远执行的存储过程的问题。它相当大，我可以理解我需要一些时间，但这个持续了将近 20 分钟。
After some debugging and researching I noticed that replacing this part of the WHEREclause;
经过一些调试和研究，我注意到替换了这部分WHERE条款；
((p_DrumNo IS NULL) OR T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))
made a HUGE difference. So the Procedure works just fine as long as p_DrumNo is NULL or I modify the above to not check if p_DrumNo is NULL;
有很大的不同。因此，只要 p_DrumNo 为 NULL，该过程就可以正常工作，或者我修改上述内容以不检查 p_DrumNo 是否为 NULL；
(T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))
The goal with this WHEREclause is to filter the result set on p_DrumNo if it′s passed in to the Stored Procedure. The WHEREclause then continues with further conditions but this specific one halts the query.
这个WHERE子句的目标是过滤 p_DrumNo 上的结果集，如果它被传递到存储过程。WHERE然后该子句继续使用更多条件，但此特定条件会停止查询。
ORDERDELIVERY is just a ~temporary table containing ORDER_IDs related to the parameter p_DrumNo.
ORDERDELIVERY 只是一个~临时表，包含与参数 p_DrumNo 相关的 ORDER_ID。
How can this simple IS NULL-check cause such big impact? It′s probably related to the use of ORtogether with the subquery but I don′t understand why as the subquery itself works just fine.
这个简单的IS NULL检查怎么会造成如此大的影响？这可能与OR与子查询一起使用有关，但我不明白为什么子查询本身工作得很好。
Thanks in advance!
提前致谢！
UPDATE[2011-09-23 10:13]
更新[2011-09-23 10:13]
I′ve broken down the problem into this small query that show the same behaviour;
我已将问题分解为显示相同行为的小查询；
Example A
示例 A
SQL query
SQL查询
SELECT * FROM T_ORDER WHERE
('290427' IS NULL OR ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%') );
Execution plan
执行计划
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
Example B
示例 B
SQL query
SQL查询
SELECT * FROM T_ORDER WHERE
( ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%') );
Execution plan
执行计划
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
As you all can see the first query (example A) makes a full table scan. Any ideas on how I can avoid this?
正如大家所见，第一个查询（示例 A）进行了全表扫描。关于如何避免这种情况的任何想法？
回答by Wolf
Instead of evaluating your procedure's parameter state in the SQL statement itself, move that evaulation to the containing PL/SQL block so it's executed only once before the ideal SQL statement is submitted. For example:
不要在 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;
I've also had some success in tuning SQL statements with an ORby breaking the statement into two mutually exclusive statements with a UNION ALL:
OR通过将语句分解为两个相互排斥的语句，我在使用 UNION ALL调整 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;
回答by ZZa
You came across such an issue due to the fact your index doesn't work if you include ORto your query. To get the same info I'd rather do this to make the index work (basing on updated query):
您遇到了这样的问题，因为如果您包含OR在查询中，您的索引将不起作用。为了获得相同的信息，我宁愿这样做以使索引工作（基于更新的查询）：
SELECT * FROM T_ORDER WHERE '290427' IS NULL
UNION ALL
SELECT * FROM T_ORDER WHERE ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%'));
It will return the same result since 290427seem to be a variable and it tends to be null or not null at the particular moment.
它将返回相同的结果，因为它290427似乎是一个变量，并且在特定时刻往往为空或不为空。
But also you can try using dynamic sqlinside you stored procedure for such purposes:
但您也可以尝试在存储过程中使用动态 sql用于此类目的：
%begin_of_the_procedure%
query_ := 'SELECT * FROM T_ORDER WHERE 1=1';
if var_ is not null then
query_ := query_||' AND ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '''||to_char(var_)||'%'')';
end if;
open cursor_ query_;
%fetching cursor loop%
%end_of_the_procedure%
And I wanted to say that I don't see sence of that IN, it'd be quite the same:
我想说我没有看到那个IN感觉，它会完全一样：
SELECT * FROM T_ORDER WHERE ('290427' IS NULL OR ORDERNO LIKE '290427%');
