StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIssue with Oracle bind variables not using index properly
text
Body
copied!<p>In my scenario, the following query runs fast (0.5 seconds on a table with 70 million rows):</p> <pre><code>select * from Purchases where (purchase_id = 1700656396) </code></pre> <p>and, it even runs fast using bind variables:</p> <pre><code>var purchase_id number := 1700656396 select * from Purchases where (purchase_id = :purchase_id) </code></pre> <p>These run fast because I have an index on the <code>purchase_id</code> column. (Keep reading...) </p> <p>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 <code>null</code>. This works fine at first.</p> <p>For example, the following query runs fast (0.5 seconds), too:</p> <pre><code>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) </code></pre> <p>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:</p> <pre><code>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) </code></pre> <p>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. </p> <p>Is it possible to avoid dynamically-constructed queries while still maintaining the same logic?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload