is Null or = null and a slow query

I have a procedure which is very slow.

This is because it works by letting the user pass in nulls to indicate that they do not have information on a particular column, if they pass in a null then the column is not included in the where clause.

 ( dStart in date, dEnd in date,
   iOptionOne in int,
   iOptionTwo in int,
   iOptionThree in int )
return number
as
  dSum1_Positive number;
begin

   select sum( PLAN1*NUM_PERIODS ) into dSum1_Positive from FINANCIAL_TRANSACTION ft, PREMIUM_COLLECTION pc

   where ft.pc_id = pc.id
   and PERIOD_END <= dEnd and PERIOD_START >= dStart

   and ( iOptionOne is null or ft.account_id = iOptionOne )
   and ( iOptionTwo is null or ft.cust_pol_id = iOptionTwo )
   and ( iOptionThree is null or ft.flop_id = iOptionThree );

It must be the "iOptionXXX is null or" which is slowing the query down.

Any Ideas on how to speed it up?

I know that I cannot rewrite the code to be e.g.

   and ( ft.account_id = iOptionOne )
   and ( ft.cust_pol_id = iOptionTwo )
   and ( ft.flop_id = iOptionThree );

because if the optional elements were set to null then nothing would match.

I could however split the code into if clauses or multiple procedures, Received on Fri Nov 04 2005 - 08:10:57 CST