Which approach is better? Dynamic PL/SQL or Generalized SQL statement

Hi,

   I have a table XYZ    

   with columns

   A     NUMBER(10),
   B     VARCHAR2(35),
   C     VARCHAR2(35),
   D     VARCHAR2(35),
   E     VARCHAR2(35),


   .         .

   .         .

   .         .

   etc..

   I have a composite unique index on (A,B,C,D,E)

   Column values A and B always exists and C,D, and E can be NULL.

   I have a generalized SQL statement like this

   SELECT * FROM XYZ
   WHERE A = :a1
   AND B = :b1

   AND NVL(C,0) = NVL(:c1,0)
   AND NVL(D,0) = NVL(:d1,0)
   AND NVL(E,0) = NVL(:e1,0)

   Because I am modifying COLUMN value oracle dosen't use C,D, or E in    index search.

   I am giving this SQL statement in a Stored Procedure.

   Does it improve the performance if I build a dynamic SQL statement and    then execute that using DBMS_SQL package ?

   If I do this it uses C,D, or E in index search if they are present. But is    there any overhead involved in using DBMS_SQL package. (i.e Does it needs    extra parsing? etc..)

   Can somebody post the reply on comp.databases.oracle.

   Thanks in advance.

   Thanks to those who replied to my previous posting.

 Murali Nemani Received on Mon Oct 30 1995 - 00:00:00 CET