explain plan, performance diffs using '%' vs. '%%' in LIKE query
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).