StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle execution plans when using the LIKE operator with a DETERMINISTIC function
text
Body
copied!<p>Now I have a really tricky thing with Oracle execution plans running havoc, when I use a <code>DETERMINISTIC</code> function on the right hand side of the <code>LIKE</code> operator. This is my situation:</p> <h2>The Situation</h2> <p>I thought it to be wise to execute a query like this (simplified):</p> <pre><code>SELECT [...] FROM customers cust JOIN addresses addr ON addr.cust_id = cust.id WHERE special_char_filter(cust.surname) like special_char_filter(?) </code></pre> <p>And I would bind <code>?</code> to something like <code>'Eder%'</code>. Now <code>customers</code> and <code>addresses</code> are very large tables. That's why it's important to use indexes. Of course, there is a regular index on <code>addresses.cust_id</code>. But I have also created a function-based index on <code>special_char_filter(customers.surname)</code>, which works quite nicely.</p> <h2>The Trouble</h2> <p>The trouble is, the above query involving a <code>like</code> clause creates execution plans with FULL TABLE SCANS on <code>addresses</code>. It looks like something in this query keeps Oracle from using indexes on <code>addresses.cust_id</code>.</p> <h2>The Workaround</h2> <p>I found out, that the solution to my problem is this:</p> <pre><code>SELECT [...] FROM customers cust JOIN addresses addr ON addr.cust_id = cust.id WHERE special_char_filter(cust.surname) like ? </code></pre> <p>I removed the (<code>DETERMINISTIC</code> !) function from the like operator's right hand side and pre-calculated the bind variable in Java. Now this query is hyper-fast, without any FULL TABLE SCANS. This, too, is very fast (although not equivalent):</p> <pre><code>SELECT [...] FROM customers cust JOIN addresses addr ON addr.cust_id = cust.id WHERE special_char_filter(cust.surname) = special_char_filter(?) </code></pre> <h2>The Confusion</h2> <p>I don't understand this. What's wrong with having deterministic functions on the right hand side of the <code>like</code> operator? I have observed this in Oracle 11.2.0.1.0</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