Here’s an example I saw a few months ago of the confusion caused by NULL. As the owner of the problem put it: the first query, run from SQL*Plus for testing purposes, takes no time to complete; but when “put into a pl/sql cursor” (as shown in the second query) it takes ages to complete.
What’s going on ?
where category = nvl(null, category);
open c_results for
where category = nvl(p_user_category, category);
fetch c_results into v_id, v_description, v_inventory;
After pondering the problem for a while the OP managed to get the required level performance by changing the predicate to this: