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?
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:
To test, simply run it. For example:
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
Just a quick question: I guess the following non-parameterized query will also run for 1.5 minutes?
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
Strange as it may sound, in this specific case two combined cross joins can help.
Look at the below example.
Sample data table:
The query:
The explain plan:
Test for :Purchase_id <> NULL
Test for :Purchase_id = NULL
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 )
obviously it uses the index, the time is 90 ms
The second query (purchase_id = null )
this query does full table scan.
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:
I tested this and it solves my issue.