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
Post a Comment