Check if parameter is NULL within WHERE clause

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)ISBEGIN    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 NULLAND ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY)UNION ALLSELECT ...FROM ...WHERE p_DrumNo IS NOT NULLAND my_column = p_DrumNo;

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 NULLUNION ALLSELECT * 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%');