Problem with Oracle Bind Variables not using index correctly

Question

In my scenario, the following query executes quickly (0.5 seconds in a table with 70 million rows):

select * from Purchases where (purchase_id = 1700656396) 

and it even works fast using binding variables:

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

They work fast because I have an index in the purchase_id column. (Keep reading...)

I need to create a query that allows you to "filter" on arbitrary columns. This means providing multiple input variables and filtering on each of them if it is not null . At first it works fine.

For example, the following query executes quickly (0.5 seconds):

 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 try to parameterize the query, either with the help of binding variables or with the help of the stored procedure, the query drastically slows down (1.5 minutes), as if it ignored 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) 

Now, in my application, I am forced to dynamically build my request at run time in order to get decent performance. This means that I am losing all the benefits of parameterized queries and making me worry about SQL injection.

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

Answer 1

This is a really big topic, but this is the approach that, in my opinion, is the 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 (necessary), and you allow Oracle to be closed when you don't have a parameter value (which is missing in your current approach). For instance:

 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 check, just run it. For instance:

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

My system uses a table over 100 mm with an index in the number field and the name field. It returns almost instantly. Also note that you may not need to select * if you do not need all the columns, but I'm a little lazy and use% rowtype for this example.

Hope that helps

Answer 2

Just a quick question: I assume the next non-parameterized query will also work 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 so, the problem is not with the binding variables, but with the lack of indexes.

EDIT The problem is that Oracle cannot decide to use the index when creating a plan for a parameterized query

Answer 3

Taking a different approach to tbone answer, I realized that I could dynamically build the query in the code and still use the bind variables (and thus gain flexibility with indexes and still be 100% protected against 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 problem.

Answer 4

Oddly enough, in this particular case two combined crosses can help.
Take a look at the example below. 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 

Request:

 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 ) ) ; 

Explanation 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 the real execution times, do not look at the plans, they lie, contain only estimates (as the oracle believes). Look at the lines with "Ca│kowity", this means "Total Runtime" (I don’t know how to change the code page to English in sqlplus). Also look at the "consistent data", this is a series of logically consistent blocks that reads the request.

First request (purchase_id <> null)

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

obviously it uses an index, time is 90 ms


Second request (purchase_id = null)

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

This query performs a full table scan.