Hi Tom,
  I have a problem in the Index Picking.

#----------------------------I have a table 
SQL> desc check_008
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 A                                                  NUMBER
 B                                                  NUMBER
 C                                                  VARCHAR2(2000)


#----------------------------Number of Records present in the table
SQL> SELECT COUNT(*)
  2    FROM check_008
  3  /

  COUNT(*)
----------
     99001

#----------------------------I have a index on the table on column (A,B)
SQL> l
  1  SELECT index_name,SUBSTR(column_name,1,20),column_position
  2    FROM all_ind_columns
  3*  WHERE table_name='CHECK_008'
SQL> /

INDEX_NAME                     SUBSTR(COLUMN_NAME,1 COLUMN_POSITION
------------------------------ -------------------- ---------------
DEL_001                        B                                  2
DEL_001                        A                                  1


SQL> set autotrace traceonly exp
SQL> DELETE FROM check_008
  2   WHERE (a=10 OR 10 IS NULL)
  3     AND (b=2  OR 2 IS NULL)
  4     AND (c='23-MAR-049218' OR '23-MAR-049218' IS NULL)
  5  /

1 row deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=19)
   1    0   DELETE OF 'CHECK_008'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CHECK_008' (Cost=14 Card=1 Bytes=19)
   3    2       INDEX (RANGE SCAN) OF 'DEL_001' (NON-UNIQUE) (Cost=7 Card=1)


Then i create a procedure with the same Delete statement like this
SQL>CREATE OR REPLACE PROCEDURE pr_without_nvl(p_a IN NUMBER,p_b IN NUMBER,p_c IN VARCHAR2,p_rows_affected OUT NUMBER)
   IS
BEGIN
 DELETE FROM check_008
  WHERE (a = p_a OR p_a IS NULL)
    AND (b = p_b OR p_b IS NULL)
    AND (c = p_c OR p_c IS NULL);
 p_rows_affected := SQL%ROWCOUNT;
END;
/
Procedure created.

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter session set timed_statistics=true;

Session altered.

SQL> variable delete_count NUMBER;
SQL> exec pr_without_nvl(10,2,'23-MAR-049218',:delete_count);

PL/SQL procedure successfully completed.

SQL> print :delete_count

DELETE_COUNT
------------
           1

By seeing the Tkprof File it looks like 



DELETE FROM CHECK_008
WHERE
 (A = :b1  OR :b1 IS NULL ) AND (B = :b3  OR :b3 IS NULL ) AND (C = :b5  OR
  :b5 IS NULL )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.00          0         70          0           0
Execute      1      0.11       0.16        206        831         24           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.13       0.16        206        901         24           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46  (ATLAS)   (recursive depth: 1)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  DELETE STATEMENT   GOAL: CHOOSE
      0   DELETE OF 'CHECK_008'
      0    TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'CHECK_008'



Where the Plan is shows as FULL Table Scan ........................????

However from this it is picking Index.

SQL> DELETE FROM check_008
  2   WHERE (a=10 OR 10 IS NULL)
  3     AND (b=2  OR 2 IS NULL)
  4     AND (c='23-MAR-049218' OR '23-MAR-049218' IS NULL)
  5  /

1 row deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=19)
   1    0   DELETE OF 'CHECK_008'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CHECK_008' (Cost=14 Card=1 Bytes=19)
   3    2       INDEX (RANGE SCAN) OF 'DEL_001' (NON-UNIQUE) (Cost=7 Card=1)


My Point is is it better to change the Query from
DELETE check_008 
 WHERE a = NVL(p_a,a)
   AND b = NVL(p_b,b)
   AND c = NVL(p_c,c);

to

DELETE check_008 
 WHERE (a = p_a OR p_a IS NULL)
   AND (b = p_b OR p_b IS NULL)
   AND (c = p_c OR p_c IS NULL)

will increase performance when there is a composite index on a and b.
Also why there is a deviation in the Plan, from the SQL*PLUS to Procedure .


Thanks And Best Regards
Sushanta  

Hi Tom,
  Many thanks for ur input. One thing to clear and it will overall clear on this concept
  When i try for this one in SQL*PLUS

SQL> variable b1 NUMBER
SQL> variable b2 NUMBER
SQL> variable b3 VARCHAR2
STAGE - I
=========
SQL> DELETE FROM CHECK_008
WHERE
 (A = :b1 ) AND (B = :b2  OR :b2 IS NULL ) AND (C = :b3  OR :b3 IS NULL )  2    3
  4  /

0 rows deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2028)
   1    0   DELETE OF 'CHECK_008'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CHECK_008' (Cost=1 Car
          d=1 Bytes=2028)

   3    2       INDEX (RANGE SCAN) OF 'DEL_008' (NON-UNIQUE)



STAGE-II
========
SQL> DELETE FROM CHECK_008
WHERE
 (A = :b1 OR :b1 IS NULL) AND (B = :b2  OR :b2 IS NULL ) AND (C = :b3  OR :b3 IS NULL )  2    3
  4  /

0 rows deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2028)
   1    0   DELETE OF 'CHECK_008'
   2    1     TABLE ACCESS (FULL) OF 'CHECK_008' (Cost=1 Card=1 Bytes=
          2028)

Colud you please clear on this that
In case of STAGE-I, ORACLE-OPTIMIZER knows that the value of the bind variable(:b1) must come,whereas in case 
of STAGE-II, ORACLE-OPTIMIZER is in "Doubt!!" , whether the value of the bind variable (:b1) will come or not, 
for that reason it will go for "FULL TABLE SCAN", without taking any RISK.

Is it so ??

Also how ORACLE generates PLAN in case of "OR" Operator ?

Many Thanks ...

Sushanta