use bind variable with LIKE % operator

Question

In my PL/SQL have this big dynamic SQL using bind variables. I use the DBMS_SQL package to bind and execute the generated query.

Based on certain criteria, where-clauses are added to the dynamic SQL. When I just use an "in" or a "=" to match my bind variables all works fine and results are returned pretty fast.

Example:

(servedparty = :bv_ or servedpartyimsi = :bv_)

However, when I do the following:

(servedpartyimei like :bv_)

and provide a value like 12345679890% the query takes a very very very long time.

I've also tried somehting like this

(servedpartyimei like :bv_||'%')

and then provide the value without the '%' but it gives the same results

When I execute the query without bind variables but just put hardcoded values, result are also returned immediatly.

Am I doing something wrong here? Don't bind variables like the LIKE operator? Any ideas?

Thank you.

Answer 1

I think you are suffering from Oracle bug 9197434 (BIND PEEKING NOT HAPPENING WHEN USING DBMS_SQL)

As far as I know, that has not been fixed.

Without the benefit of bind variable peeking, Oracle has no idea what value will be on the the right-hand side of your LIKE condition. It could just be '%', for instance. So, Oracle makes assumptions about how many rows will be LIKE a typical bind variable value. Those assumptions are pretty conservative and are likely forcing Oracle away from the fast plan you want (using an index, likely) to the slow plan you are getting (using a hash-join, likely).

I would recommend you use Native Dynamic SQL (i.e., EXECUTE IMMEDIATE), if possible, as that does not suffer from this bug. Otherwise, you may need to HINT your SQL.