Issue with Oracle bind variables not using index properly

Question

In my scenario, the following query runs fast (0.5 seconds on a table with 70 million rows):

select * from Purchases
where (purchase_id = 1700656396)

and, it even runs fast using bind variables:

var purchase_id number := 1700656396
select * from Purchases
where (purchase_id = :purchase_id)

These run fast because I have an index on the purchase_id column. (Keep reading...)

I need to create a query that allows "filtering" on arbitrary columns. This means providing several input variables, and filtering on each one unless it is null. This works fine at first.

For example, the following query runs fast (0.5 seconds), too:

select * from Purchases
where (1700656396 IS NULL OR purchase_id    = 1700656396)
and   (NULL       IS NULL OR purchase_name  = NULL)
and   (NULL       IS NULL OR purchase_price = NULL)

But, when I attempt to parameterize the query, either by bind variables or stored procedure, the query slows down dramatically (1.5 minutes), as if it is ignoring any indexes:

var purchase_id    number   := 1700656396
var purchase_name  varchar2 := NULL
var purchase_price number   := NULL
select * from Purchases
where (:purchase_id    IS NULL OR purchase_id    = :purchase_id)
and   (:purchase_name  IS NULL OR purchase_name  = :purchase_name)
and   (:purchase_price IS NULL OR purchase_price = :purchase_price)

Right now, in my application, I am forced to dynamically construct my query at run-time in order to get decent performance. This means I lose all the advantages of parameterized queries, and forces me to worry about SQL injection.

Is it possible to avoid dynamically-constructed queries while still maintaining the same logic?

Have you tried doing EXPLAIN PLAN and seeing what is going on under the covers? Also, what version of Oracle are you on? — Thomas Erdman, Jul 16 '13 at 16:07
Explain plan doesn't help when using bind variables (http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#i25909) — Jay Sullivan, Jul 16 '13 at 16:19
I'm using Oracle 11.1.0.7.0. I updated my tag to include this. — Jay Sullivan, Jul 16 '13 at 16:21
The explain plan will still tell you what Oracle thinks it's going to do, even if it ends up doing something different (which you could trace). It would still be a good starting point. Do you have indexes on `purchase_name` or `purchase_price`? — Alex Poole, Jul 16 '13 at 16:57
I only have an index on `purchase_id` in this scenario, not the others, nor do I want them on the others, I expect the query to run slow if I provide values for those columns. — Jay Sullivan, Jul 16 '13 at 17:24
@Thomas/Alex: The EXPLAIN PLAN tells me pretty much what the slowness is already telling me: when it took 1.5 minutes, it's doing a table scan, and when it took 0.5 seconds, it's using the index. I am wondering (1) why can't Oracle recognize that `NULL IS NULL` should be skipped, and (2) is there any way to get my desired behavior using a parameterized query or stored procedure? — Jay Sullivan, Jul 16 '13 at 17:42
Have you considered using a hint to try to force Oracle to use the index rather than doing a full table scan? — Thomas Erdman, Jul 16 '13 at 17:58
@ThomasErdman - won't that make it worse when `:purchase_id` is in fact `null`? — Alex Poole, Jul 16 '13 at 18:04
@AlexPoole, I believe that you can use an Anonymous PL/SQL block to say if the bind variable is null, use one version of the query, if else, use the the other (with hints). — Thomas Erdman, Jul 16 '13 at 18:09
@ThomasErdman - sure, though a hint wouldn't be needed then; but that's a departure from the current pattern. Might be worth writing that up as an answer though. I'm not sure what the real application client is, so a different JDBC call or whatever might be the end result, rather than a block . Of course, if that bind is null then it'll have to do a FTS anyway... — Alex Poole, Jul 16 '13 at 18:14

tbone · Accepted Answer · 2013-07-17T15:25:26.377

This is a bigger topic really, but this is the approach that I think is easiest to implement and works well. The trick is to use dynamic SQL, but implement it so that you always pass the same number of parameters (needed), AND you allow Oracle to short-circuit when you don't have a value for a parameter (what you are lacking in your current approach). For example:

set serveroutput on
create or replace procedure test_param(p1 in number default null, p2 in varchar2 default null) as
  l_sql varchar2(4000);
  l_cur sys_refcursor;
  l_rec my_table%rowtype;
  l_ctr number := 0;
begin

  l_sql := 'select * from my_table where 1=1';
  if (p1 is not null) then
    l_sql := l_sql || ' and my_num_col = :p1';
  else
    -- short circuit for optimizer (1=1)
    l_sql := l_sql || ' and (1=1 or :p1 is null)';
  end if;

  if (p2 is not null) then
    l_sql := l_sql || ' and name like :p2';
  else
    -- short circuit for optimizer (1=1)
    l_sql := l_sql || ' and (1=1 or :p2 is null)';
  end if;

  -- show what the SQL query will be
  dbms_output.put_line(l_sql);

  -- note always have same param list (using)
  open l_cur for l_sql using p1,p2;

  -- could return this cursor (function), or simply print out first 10 rows here for testing
  loop
    l_ctr := l_ctr + 1;
    fetch l_cur
    into l_rec;
    exit when l_cur%notfound OR l_ctr > 10;

    dbms_output.put_line('Name is: ' || l_rec.name || ', Address is: ' || l_rec.address1);
  end loop;
  close l_cur;
end;

To test, simply run it. For example:

set serveroutput on
-- using 0 param
exec test_param();
-- using 1 param
exec test_param(123456789);
-- using 2 params
exec test_param(123456789, 'ABC%');

On my system, the table used is over 100mm rows with an index on the number field and name field. Returns almost instantly. Also note that you may not want to do a select * if you don't need all columns, but I'm being a bit lazy and using %rowtype for this example.

Hope that helps

bpgergo · Answer 2 · 2013-07-16T17:54:17.123

1

Just a quick question: I guess the following non-parameterized query will also run for 1.5 minutes?

select * from Purchases
where (1700656396 IS NULL OR purchase_id    = 1700656396)
and   ('some-name' IS NULL OR purchase_name  = 'some-name')
and   (12       IS NULL OR purchase_price = 12)

If yes, the problem is not the bind variables but the lack of indexes.

EDIT The problem is, Oracle cannot decide to use the index when generating the plan for the parametrized query

edited Jul 16 '13 at 17:54

answered Jul 16 '13 at 17:03

bpgergo

15,669
5
44
68

That will *require* a table-scan, because it's providing a value for non-indexed columns `purchase_name` and `purchase_price`—i.e, there is *no possible way* oracle could run that query without a table scan. However, for the query I provided, it is easy to see that `NULL IS NULL` should always evaluate to true, and thus these `WHERE` clauses can be skipped (and indeed, Oracle does just that for the non-bind-variable query). And I don't think "The problem is lack of indexes"—I really don't want to have to index every column, I just expect some inputs to allow it to run faster than others. – Jay Sullivan Jul 16 '13 at 17:22
1

OK, basically we're on the same platform. The problem is, as far as I know, the plan is generated for parametrized queries _before_ the parameters are evaluated. That makes it possible to reuse the plan, thus save the effort to generate it again when you run the same query with different parameters. How would the Oracle find out if the index on purchase_id field can be used or full table scan is needed from the parametrized query? – bpgergo Jul 16 '13 at 17:53
@bpgergo - this is improved in 11gR2. What you've shown won't require a full table scan, having non-indexed columns in the filter doesn't preclude the `purchase_id` index being used. Since that is the only index in play, I'd expect this to perform exactly the same as the `null` version (third query in the question). The plan will just have different filters, it won't affect access. – Alex Poole Jul 16 '13 at 18:02

score 1 · Answer 3 · answered Jul 18 '13 at 13:09

Taking a different approach to tbone's answer, I realized that I can dynamically construct the query in code, and still use bind variables (and thus gain flexibility with indexes, and still be 100% protected from SQL injection).

In my code, I can do something like this:

string sql = "select * from Purchases where 1 = 1";
if(purchase_id != null)    sql += " and (purchase_id = :purchase_id)";
if(purchase_name != null)  sql += " and (purchase_name = :purchase_name)";
if(purchase_price != null) sql += " and (purchase_price = :purchase_price)";

I tested this and it solves my issue.

score 1 · Answer 4 · answered Jul 21 '13 at 11:26

Strange as it may sound, in this specific case two combined cross joins can help.
Look at the below example.

Sample data table:

select * from all_tables;
drop table Purchases;
create table Purchases as
select zx.object_id + (lev-1) * 100000 purchase_id, 
          object_name purchase_name,
          round( dbms_random.value( 1, 200 )) purchase_price,
          zx.* 
from all_objects zx
cross join (select level lev from dual connect by level <= 170);

create unique index purchases_id_ix on Purchases( Purchase_id );

exec dbms_stats.gather_table_stats( user, 'Purchases' );

select count(*) from Purchases;

  COUNT(*)
----------
  10316620

The query:

var Purchase_id varchar2( 4000 )
var Purchase_name varchar2( 4000 )
var Purchase_price varchar2( 4000 )

begin
  :Purchase_id := '1139';
  :Purchase_name := NULL;
  :Purchase_price := NULL;
end;
    /

explain plan for
select p.* 
from Purchases p
cross join (
  select 1 from dual d
  where :Purchase_id is not null
) part_1
where Purchase_id = to_number( :Purchase_id )
  and ( :Purchase_name is null or Purchase_name = :Purchase_name )
  and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
union all
select p.* 
from Purchases p
cross join (
  select 1 from dual d
  where :Purchase_id is null
) part_2
where 
  ( :Purchase_name is null or Purchase_name = :Purchase_name )
  and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
;

The explain plan:

Plan hash value: 460094106

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    | 28259 |  5546K| 54093   (1)| 00:10:50 |
|   1 |  NESTED LOOPS                   |                    | 28259 |  5546K| 54093   (1)| 00:10:50 |
|   2 |   FAST DUAL                     |                    |     1 |       |     2   (0)| 00:00:01 |
|   3 |   VIEW                          | VW_JF_SET$96C1679A | 28259 |  5546K| 54091   (1)| 00:10:50 |
|   4 |    UNION-ALL                    |                    |       |       |            |          |
|*  5 |     FILTER                      |                    |       |       |            |          |
|*  6 |      TABLE ACCESS BY INDEX ROWID| PURCHASES          |     1 |   132 |     3   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | PURCHASES_ID_IX    |     1 |       |     2   (0)| 00:00:01 |
|*  8 |     FILTER                      |                    |       |       |            |          |
|*  9 |      TABLE ACCESS FULL          | PURCHASES          | 28258 |  3642K| 54088   (1)| 00:10:50 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(:PURCHASE_ID IS NOT NULL)
   6 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
              (:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
   7 - access("P"."PURCHASE_ID"=TO_NUMBER(:PURCHASE_ID))
   8 - filter(:PURCHASE_ID IS NULL)
   9 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
              (:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))

27 wierszy zosta│o wybranych.

Test for :Purchase_id <> NULL

SQL> set pagesize 0
SQL> set linesize 200
SQL> set timing on
SQL> set autotrace traceonly
SQL>
SQL> begin
  2    :Purchase_id := '163027';
  3    :Purchase_name := NULL;
  4    :Purchase_price := NULL;
  5  end;
  6  /

Procedura PL/SQL zosta│a zako˝czona pomyťlnie.

Ca│kowity: 00:00:00.00
SQL> select p.*
  2  from Purchases p
  3  cross join (
  4    select 1 from dual d
  5    where :Purchase_id is not null
  6  ) part_1
  7  where Purchase_id = to_number( :Purchase_id )
  8    and ( :Purchase_name is null or Purchase_name = :Purchase_name )
  9    and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
 10  union all
 11  select p.*
 12  from Purchases p
 13  cross join (
 14    select 1 from dual d
 15    where :Purchase_id is null
 16  ) part_2
 17  where
 18    ( :Purchase_name is null or Purchase_name = :Purchase_name )
 19    and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
 20  ;

Ca│kowity: 00:00:00.09

Plan wykonywania
----------------------------------------------------------
Plan hash value: 460094106

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    | 28259 |  5546K| 54093   (1)| 00:10:50 |
|   1 |  NESTED LOOPS                   |                    | 28259 |  5546K| 54093   (1)| 00:10:50 |
|   2 |   FAST DUAL                     |                    |     1 |       |     2   (0)| 00:00:01 |
|   3 |   VIEW                          | VW_JF_SET$96C1679A | 28259 |  5546K| 54091   (1)| 00:10:50 |
|   4 |    UNION-ALL                    |                    |       |       |            |          |
|*  5 |     FILTER                      |                    |       |       |            |          |
|*  6 |      TABLE ACCESS BY INDEX ROWID| PURCHASES          |     1 |   132 |     3   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | PURCHASES_ID_IX    |     1 |       |     2   (0)| 00:00:01 |
|*  8 |     FILTER                      |                    |       |       |            |          |
|*  9 |      TABLE ACCESS FULL          | PURCHASES          | 28258 |  3642K| 54088   (1)| 00:10:50 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(:PURCHASE_ID IS NOT NULL)
   6 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
              (:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
   7 - access("P"."PURCHASE_ID"=TO_NUMBER(:PURCHASE_ID))
   8 - filter(:PURCHASE_ID IS NULL)
   9 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
              (:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))


Statystyki
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size
       1865  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Test for :Purchase_id = NULL

SQL> begin
  2    :Purchase_id := NULL;
  3    :Purchase_name := 'DBMS_CUBE_UTIL';
  4    :Purchase_price := NULL;
  5  end;
  6  /

Procedura PL/SQL zosta│a zako˝czona pomyťlnie.

Ca│kowity: 00:00:00.00
SQL> select p.*
  2  from Purchases p
  3  cross join (
  4    select 1 from dual d
  5    where :Purchase_id is not null
  6  ) part_1
  7  where Purchase_id = to_number( :Purchase_id )
  8    and ( :Purchase_name is null or Purchase_name = :Purchase_name )
  9    and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
 10  union all
 11  select p.*
 12  from Purchases p
 13  cross join (
 14    select 1 from dual d
 15    where :Purchase_id is null
 16  ) part_2
 17  where
 18    ( :Purchase_name is null or Purchase_name = :Purchase_name )
 19    and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
 20  ;

510 wierszy zosta│o wybranych.

Ca│kowity: 00:00:11.90

Plan wykonywania
----------------------------------------------------------
Plan hash value: 460094106

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    | 28259 |  5546K| 54093   (1)| 00:10:50 |
|   1 |  NESTED LOOPS                   |                    | 28259 |  5546K| 54093   (1)| 00:10:50 |
|   2 |   FAST DUAL                     |                    |     1 |       |     2   (0)| 00:00:01 |
|   3 |   VIEW                          | VW_JF_SET$96C1679A | 28259 |  5546K| 54091   (1)| 00:10:50 |
|   4 |    UNION-ALL                    |                    |       |       |            |          |
|*  5 |     FILTER                      |                    |       |       |            |          |
|*  6 |      TABLE ACCESS BY INDEX ROWID| PURCHASES          |     1 |   132 |     3   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | PURCHASES_ID_IX    |     1 |       |     2   (0)| 00:00:01 |
|*  8 |     FILTER                      |                    |       |       |            |          |
|*  9 |      TABLE ACCESS FULL          | PURCHASES          | 28258 |  3642K| 54088   (1)| 00:10:50 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(:PURCHASE_ID IS NOT NULL)
   6 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
              (:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
   7 - access("P"."PURCHASE_ID"=TO_NUMBER(:PURCHASE_ID))
   8 - filter(:PURCHASE_ID IS NULL)
   9 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
              (:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))


Statystyki
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     197993  consistent gets
      82655  physical reads
          0  redo size
      16506  bytes sent via SQL*Net to client
        882  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        510  rows processed

To know real execution times, do not look at plans, they tell lies, contain only estimates (how oracle thinks it would be). Look at rows with "Ca│kowity", it means "Total execution time" (I don't know how to change a codepage to english in sqlplus). Also look at "consistent gets", this is a number of logical consistent blocks that the query reads.

The first query (purchase_id <> null )

Ca│kowity: 00:00:00.09
          4  consistent gets
          2  physical reads

obviously it uses the index, the time is 90 ms

The second query (purchase_id = null )

Ca│kowity: 00:00:11.90
     197993  consistent gets
      82655  physical reads

this query does full table scan.

Issue with Oracle bind variables not using index properly

4 Answers4

Linked