We ran into a performance issue where all of the top queries in our AWR report were doing a LIKE comparison of a NUMBER column against a character literal like this:
customer_number LIKE ‘123456%’
customer_number is a selective predicate and has an index but the index wasn’t being used. Also the number in the like condition 123456 in the example was the complete customer number. So, we should just be doing this:
customer_number = 123456
So, I built a test script to test this out. One interesting thing in my test script was that comparing the number column to a character literal with the = operator didn’t suppress this index. But using LIKE did. If you look at the “Predicate Information” section of the plans generated for each query you can see why this is. In my test script the column of type number is called object_id.
First I did a query with object_id=2, which is comparing a number to a number. This query used the index and the predicate looked like this:
access(“OBJECT_ID”=2)
But, then I ran the same query with object_id = ‘2’ comparing a number column to a character literal. But this query also used the index and had the same predicate as above. So, the character literal ‘2’ must first have been converted to the number 2 so the index could be used.
Next I tried object_id like ‘2’ and this caused a full table scan and the predicate looked like this:
filter(TO_CHAR(“OBJECT_ID”)=’2′)
I think that in order to do a LIKE the object_id had to first be converted to a character string so it could be compared with the literal. In my test case I didn’t even put in a wildcard character but it didn’t matter.
Lastly, I tried an index on to_char(object_id) and this index was picked up with the like.
So, if you want to take a number and do a LIKE against it just be aware that the optimizer will convert it to a character string first and that any index on the number column will not be used.
– Bobby
