Oracle like statement not using correct index

Question

Oracle database.

I've got the following segment of SQL that's performing a full table scan on PROVIDER P1 table. I believe this is because it's dynamically building a like clause as you can see on line XXX.

I've got an index on PROVIDER.TERMINAL_NUMBER and the following SQL snippet does use the correct index.

select * from providers where terminal_number like '1234%'

so why does the following not hit that index?

SELECT P1.PROVIDER_NUMBER, P1.TERMINAL_NUMBER, PC."ORDER" FROM PROVIDERS P1
  INNER JOIN PROVIDER_CONFIG PC
         ON PC.PROVIDER_NUMBER = P1.PROVIDER_NUMBER
WHERE EXISTS (
  SELECT E2.* FROM EQUIPMENT E1
    INNER JOIN EQUIPMENT E2
            ON E1.MERCHANT_NUMBER = E2.MERCHANT_NUMBER
  WHERE E1.TERMINAL_NUMBER = 'SA323F'
  AND E1.STATUS IN (0, 9)
  AND E2.STATUS IN (0, 9)
  XXX 
  AND P1.TERMINAL_NUMBER LIKE SUBSTR(E2.TERMINAL_NUMBER, 0, length(E2.TERMINAL_NUMBER) - 1) || '%'
)
ORDER BY PC."ORDER" DESC

Answer 1

Here ...

 select * from providers where terminal_number like '1234%'

... the Optimiser knows all the fitting numbers start with a fixed prefix and so will be co-located in the index. Hence reading the index is likely to be very efficient.

But here there is no such knowledge ...

P1.TERMINAL_NUMBER LIKE SUBSTR(E2.TERMINAL_NUMBER, 0, length(E2.TERMINAL_NUMBER) - 1) || '%'

There can be any number of different prefixes from E2.TERMINAL_NUMBER and the query will be returning records from all over the PROVIDERS table. So indexed reads will be highly inefficient, and a blunt approach of full scans is the right option.

It may be possible to rewrite the query so it works more efficiently - for instance you would want a Fast Full Index Scan rather than a Full Table Scan. But without knowing your data and business rules we're not really in a position to help, especially when dynamic query generation is involved.

One thing which might improve performance would be to replace the WHERE EXISTS with a WHERE IN...

SELECT P1.PROVIDER_NUMBER, P1.TERMINAL_NUMBER, PC."ORDER" FROM PROVIDERS P1
  INNER JOIN PROVIDER_CONFIG PC
         ON PC.PROVIDER_NUMBER = P1.PROVIDER_NUMBER
WHERE substr(P1.TERMINAL_NUMBER, 1, 5) IN  (
  SELECT SUBSTR(E2.TERMINAL_NUMBER, 1, 5)
    FROM EQUIPMENT E1
    INNER JOIN EQUIPMENT E2
            ON E1.MERCHANT_NUMBER = E2.MERCHANT_NUMBER
  WHERE E1.TERMINAL_NUMBER = 'SA323F'
  AND E1.STATUS IN (0, 9)
  AND E2.STATUS IN (0, 9)
)
ORDER BY PC."ORDER" DESC

This would work if the length of the terminal number is constant. Only you know your data, so only you can tell whether it will fly.