title: Oracle stored procedure not using index when using case statement in a where clause
body: <p>I need to query a table and can pass either one or up to six parameters in the WHERE clause depending on the options chosen on the front end. I used a CASE statement in WHERE clause so as to deal with all permutations.</p>
  
  <p>Here is the SP code snippet:</p>
  
  <pre><code>create procedure return_data ( 
       p_field1 in varchar(20), 
       p_field2 in varchar2(30), 
       p_field3 in varchar2(30), 
       cur out sys_refcursor)
  is 
  begin
      open cur for
          select col1, col2, col3,col4,col5,col6 
          from master_table
          where (case when (p_field1 is null) then (1) 
                     when (p_field1 is not null) and (col1=p_field1) then 1 
                     else 0 end) =1 
         and (case when (p_field2 is null) then (1) 
                   when (p_field2 is not null) and (col2=p_field2) then 1 
                   else 0 end) =1 
        ... so one repeat for all columns.
  </code></pre>
  
  <p>However, master_table has over 5 million rows so I created an index on each of the search fields but the stored procedure is not using the index and is doing full table scan, resulting in slow performance.</p>
  
  <p>If I remove the CASE statement from WHERE clause and pass something like <code>where col1=p_field1)</code> the index is used and the query performance is very good.</p>
  
  <p>However, as not all the input fields are mandatory from the front end, I have to use CASE statement in WHERE clause.</p>
  
  <p>Precisely, with CASE statement in WHERE clause, Oracle is not using index.</p>
  
  <p>Can someone suggest how to tune the above sql so it uses index for fast performance?</p>
  
  <p>Thanks in advance.</p>