Oracle语句未使用正确的索引

Question

Oracle数据库

下面的SQL段正在对PROVIDER P1表执行完整的表扫描。我相信这是因为它动态地构建了一个like子句，就像你在XXX行上看到的那样。

我已经在PROVIDER.TERMINAL_NUMBER上找到了一个索引，下面的SQL片段确实使用了正确的索引。

select * from providers where terminal_number like '1234%'

那么，为什么以下这些没有达到这个指数呢？

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

在这里..。

 select * from providers where terminal_number like '1234%'

..。Optimiser知道所有的拟合数字都以一个固定的前缀开头，因此将在索引中同时放置。因此，阅读指数可能是非常有效的。

但这里没有这样的知识..。

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

E2.TERMINAL_NUMBER中可以有任意数量的不同前缀，查询将从整个PROVIDERS表返回记录。因此，索引读取将是非常低效率的，而直截了当的全扫描方法是正确的选择。

重写查询可能是可能的，因此它的工作效率更高--例如，您需要快速全索引扫描，而不是全表扫描。但是，如果不了解您的数据和业务规则，我们就无法提供帮助，特别是在涉及动态查询生成时。

可以提高性能的一件事是用WHERE替换WHERE .

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

如果终端号码的长度是恒定的，这将是可行的。只有你知道你的数据，所以只有你能知道它是否会飞。

Answer 2

如果此查询不使用索引：

select *
from providers
where terminal_number like '1234%'

那么大概terminal_number是数字而不是字符串。类型转换阻止索引的使用。

如果要使用索引，则将值转换为字符串并使用字符串索引：

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

然后将查询写成：

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