Oracle like statement not using correct index

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

2 answers

  • answered 2018-07-12 07:35 APC

    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.

  • answered 2018-07-12 11:36 Gordon Linoff

    If this query does not use an index:

    select *
    from providers
    where terminal_number like '1234%'
    

    Then presumably terminal_number is numeric and not a string. The type conversion prevents the use of the index.

    If you want to use an index, then convert the value to a string and use a string index:

    create index idx_providers_terminal_number_str on providers(cast(terminal_number as varchar2(255)));
    

    Then write the query as:

    select *
    from providers
    where cast(terminal_number as varchar2(255)) like '1234%'