Check if parameter is NULL within WHERE clause

Question

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.

After some debugging and researching I noticed that replacing this part of the WHERE clause;

((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;

(T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))

The goal with this WHERE clause is to filter the result set on p_DrumNo if it´s passed in to the Stored Procedure. The WHERE clause then continues with further conditions but this specific one halts the query.

ORDERDELIVERY is just a ~temporary table containing ORDER_IDs related to the parameter p_DrumNo.

How can this simple IS NULL-check cause such big impact? It´s probably related to the use of OR together with the subquery but I don´t understand why as the subquery itself works just fine.

Thanks in advance!

UPDATE [2011-09-23 10:13]

I´ve broken down the problem into this small query that show the same behaviour;

Example A

SQL query

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

SQL query

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?

Answer 1

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:

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 OR by breaking the statement into two mutually exclusive statements with a 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;

Answer 2

You came across such an issue due to the fact your index doesn't work if you include OR to your query. To get the same info I'd rather do this to make the index work (basing on updated query):

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 290427 seem to be a variable and it tends to be null or not null at the particular moment.

But also you can try using dynamic sql inside you stored procedure for such purposes:

%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:

SELECT * FROM T_ORDER WHERE ('290427' IS NULL OR ORDERNO LIKE '290427%');