Is there a better way to handle null parameters? [message #365318] |
Thu, 11 December 2008 07:43  |
bpeasey
Messages: 46 Registered: March 2005
|
Member |
|
|
Hi,
We have 55 procedures where the possibility of null parameters exist. If a given parameter is not null we want to use it in the where clause. Currently a where clause looks like this:
WHERE p_product_id is null or product.product_id = p_product_id AND..
The null seems to cause the optimizer to not use the index on product_id. Which is causing bad explain plans.
One work around is to convert the query to dynamic sql and parse in the where clause with parameters that are not null.
v_WhereClause := '';
IF (p_product_id IS NOT NULL) THEN
v_WhereClause := v_WhereClause || chr(10) || chr(13) ||
' product.product_id = ' || p_product_id;
END IF;
Solves the index problem but makes debugging and parse times a problem.
Surely there is a better solution?
Thanks in advance.
Brian
|
|
|
|
|
|
Re: Is there a better way to handle null parameters? [message #366197 is a reply to message #366187] |
Fri, 12 December 2008 01:36  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
rap.fernandes wrote on Fri, 12 December 2008 07:56 | Hi,
I think you can also use the following,
product.product_id = NVL(p_product_id, product.product_id)
Regards
Raphael.
|
But optimzer might be unable to use index with this.
Let it simple for the optimizer, the more simple and straight is your query the better is the job of the optimizer.
Regards
Michel
|
|
|