Oracle stored procedure not using index when using case statement in a where clause

Question

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.

Here is the SP code snippet:

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.

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.

If I remove the CASE statement from WHERE clause and pass something like where col1=p_field1) the index is used and the query performance is very good.

However, as not all the input fields are mandatory from the front end, I have to use CASE statement in WHERE clause.

Precisely, with CASE statement in WHERE clause, Oracle is not using index.

Can someone suggest how to tune the above sql so it uses index for fast performance?

Thanks in advance.

Answer 1

You have written one query which is actually 64 (2^6) queries. Different permutations of parameters will suit different access paths.

The optimizer creates a specific access path for a query based on everything it knows about the table. For instance, how selective are these columns used in a WHERE clause?

• Do any of the columns have a unique index? That's good for indexed lookups.
• Do the histograms indicate a column has only three values, split evenly across the rows? That's bad for indexed lookups.
• What about the clustering factor? If an indexed column has values spread across every table block it's more efficient to use a Full Table Scan than read the index.
• Is this column is highly skewed, so that one or two values comprise a majority of the entries? An indexed read would be disastrous for those two values but very efficient for the long tail of other values.

So that's the challenge you're setting the optimizer: come up with a single execution plan which will work for whatever permutation of parameters the user passes. Of course it can't do that. The snag is, there is one permutation of parameters which can only be serviced by a Full Table Scan and that's when all six parameters are null. (You say that the stored procedure passes at least one parameter every time but that's enforced externally to the query, so the optimizer doesn't know that.) Also some indexed columns may be so unselective as to be catastrophic. Hence it seems likely that the optimizer is choosing to use Full Table Scan as the least dangerous for all possible permutations, even though that ends up being inefficient for most permutations.

What to do about this?

One approach is to ask the optimizer to re-parse the query each time it's run. There are complicated ways of doing this (mis-)using Row Level Security but perhaps all you need do is apply the /*+ BIND_AWARE */ hint to your query.

Alternatively, use dynamic SQL. Literally write a different SQL statement depending on which parameters are populated. Like this

...
is 
   stmt varchar2(32767);
begin
    stmt := '
        select col1, col2, col3,col4,col5,col6 
        from master_table
        where 1=1';
    if p_field1 is not null then
        stmt := stmt || ' and col1 = '''|| p_field1 ||'''';
    end if;
    if p_field2 is not null then
        stmt := stmt || ' and col2 = '''|| p_field2 ||'''';
    end if;
    if p_field3 is not null then
        stmt := stmt || ' and col3 = '''|| p_field3 ||'''';
    end if;
    open cur for stmt;

Note: in the above code I have chosen not to use bind variables. My main reason for doing this is the need to 64 permutations of parameters in the OPEN .., USING statement. However, @WilliamRobertson has suggested a link to a Tom Kyte article which has a neat way of handling this. Find out more.

Here is my code written along the Tom Kyte lines:

...
is 
   stmt varchar2(32767);
begin
    stmt := '
        select col1, col2, col3,col4,col5,col6 
        from master_table';
    if p_field1 is not null then
        stmt := stmt || ' where col1 = :p_field1 ';
    else 
        stmt := stmt || ' where (1 = 1 or :p_field1 is null)';
    end if;
    if p_field2 is not null then
        stmt := stmt || ' and col2 =:p_field2 ';
    else 
        stmt := stmt || ' and (1 = 1 or :p_field2 is null)';
    end if;
    if p_field3 is not null then
        stmt := stmt || ' and col3 = :p_field3 ';
    else 
        stmt := stmt || ' and (1 = 1 or :p_field3 is null)';
    end if;
    open cur for stmt
        using p_field1, p_field2, p_field3;