oracle - use bind variable with LIKE % operator -


in pl/sql have big dynamic sql using bind variables. use dbms_sql package bind , execute generated query.

based on criteria, where-clauses added dynamic sql. when use "in" or "=" match bind variables works fine , results returned pretty fast.

example:

(servedparty = :bv_ or servedpartyimsi = :bv_)

however, when following:

(servedpartyimei :bv_) 

and provide value 12345679890% query takes very long time.

i've tried somehting this

(servedpartyimei :bv_||'%')

and provide value without '%' gives same results

when execute query without bind variables put hardcoded values, result returned immediatly.

am doing wrong here? don't bind variables like operator? ideas?

thank you.

i think suffering oracle bug 9197434 (bind peeking not happening when using dbms_sql)

as far know, has not been fixed.

without benefit of bind variable peeking, oracle has no idea value on the right-hand side of like condition. '%', instance. so, oracle makes assumptions how many rows like typical bind variable value. assumptions pretty conservative , forcing oracle away fast plan want (using index, likely) slow plan getting (using hash-join, likely).

i recommend use native dynamic sql (i.e., execute immediate), if possible, not suffer bug. otherwise, may need hint sql.


Comments

Popular posts from this blog

xcode - CocoaPod Storyboard error: -

c# - AutoMapper - What's difference between Condition and PreCondition -