Home » SQL & PL/SQL » SQL & PL/SQL » Is there a better way to handle null parameters?
Is there a better way to handle null parameters? [message #365318] Thu, 11 December 2008 07:43 Go to next message
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 #365323 is a reply to message #365318] Thu, 11 December 2008 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it is the way you have to do it.
Take care to put parenthesis.

Regards
Michel
Re: Is there a better way to handle null parameters? [message #366187 is a reply to message #365318] Fri, 12 December 2008 00:56 Go to previous messageGo to next message
rap.fernandes
Messages: 4
Registered: June 2008
Junior Member
Hi,

I think you can also use the following,

product.product_id = NVL(p_product_id, product.product_id)

Regards

Raphael.
Re: Is there a better way to handle null parameters? [message #366188 is a reply to message #365323] Fri, 12 December 2008 00:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this is an ugly problem no matter how you slice it. However there are things that can be done if you are willing to work a bit. For example: although you have 55 parameters, I find it hard to believe that all of them go to the same where clause.

If your situation is more like 5 here , 3 there than you can do this:

if product_id_p is null and client_id_p is null then
   select * from ...;
if product_id_p is null and client_id is not null then
   select * from ... where client_id = client_id_p
if product_id_p is not null and client_id is null then
   select * from ... where product_id = product_id_p
if product_id_p is not null and client_id is not null then
   select * from ... where client_id = client_id_p and product_id = product_id_p
end if;

Obviously, the larger the number of parameters that go into a single where clause the greater the permutations and the more queries. You can easily generate all the code you require by using sql from sql, but after 5 parameters it starts to get rediculously big.

If your situation is you wish to use an index based on some combination of columns then you could do try using a function based index. Something like this:

create index i1 on t1 (product_id||'.'||client_id||'.'||...);

Then in your code do this:

select from t1 where product_id||'.'||client_id||'.'... = product_id_p||'.'||client_id_p||'.'...;

Now you have only one query, the index always gets used, and the number of parameters can get really big as long as the length of the index entries do not exceed some index size limit (depends on you release of oracle).

These are ideas you can explore. If either will work really depends upon your actual business situation.

Good luck, Kevin
Re: Is there a better way to handle null parameters? [message #366197 is a reply to message #366187] Fri, 12 December 2008 01:36 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Split value and sort by lastname
Next Topic: Error Pl/SQL collection.
Goto Forum:
  


Current Time: Mon Dec 05 19:10:52 CST 2016

Total time taken to generate the page: 0.09345 seconds