SQL Performance (MOSC)

MOSC Banner

explain plan, performance diffs using '%' vs. '%%' in LIKE query

edited Jul 29, 2014 10:14AM in SQL Performance (MOSC) 4 commentsAnswered

I have an SR opened on this, but throwing it out here too in case someone has seen it before:

We are seeing a strange result when querying using the LIKE operator. The initial problem presents as follows: SQL queries auto-generated by application code include the following:

AND F.CUST_ID LIKE '%'

AND B.CONTRACT_NUM LIKE '%'

The requirement is to return all CUST_IDs, CONTRACT_NUMs, etc.The above SQL produces decent explain plans but very poor performance.

If we change the '%' to '%%', we get a better plan (index is used) and query performance is very good. Note that the '%%' is not producing incorrect results (e.g. CUST_IDs containing the '%' character).

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center