Oracle查询在索引编号列上使用“赞”，性能不佳

Question

在查询1上，即使id是索引列，也会执行全表扫描。查询2达到相同的结果，但速度更快。如果运行查询1返回索引列，则它会快速返回，但是如果返回未索引列或整个行，则查询将花费更长的时间。

在查询3中，它运行很快，但是列“代码”是VARCHAR2（10）而不是NUMBER（12），并且以与“ id”相同的方式索引。

为什么查询1不选择应使用索引？是否应该更改某些内容以使索引数字列更快地执行？

[查询1]

select a1.*
from people a1
where a1.id like '119%' 
and rownum < 5

Explain Plan
SELECT STATEMENT ALL_ROWS
Cost: 67 Bytes: 2,592 Cardinality: 4
2 COUNT STOPKEY
1 TABLE ACCESS FULL TABLE people
Cost: 67 Bytes: 3,240 Cardinality: 5

[查询2]

select a1.*
from people a1, people a2
where a1.id = a2.id
and a2.id like '119%' 
and rownum < 5

Explain Plan
SELECT STATEMENT ALL_ROWS
Cost: 11 Bytes: 2,620 Cardinality: 4
5 COUNT STOPKEY
4 TABLE ACCESS BY INDEX ROWID TABLE people
Cost: 3 Bytes: 648 Cardinality: 1
3 NESTED LOOPS
Cost: 11 Bytes: 2,620 Cardinality: 4
1 INDEX FAST FULL SCAN INDEX people_IDX3
Cost: 2 Bytes: 54,796 Cardinality: 7,828
2 INDEX RANGE SCAN INDEX people_IDX3
Cost: 2 Cardinality: 1

[查询3]

select a1.*
from people a1
where a1.code like '119%' 
and rownum < 5

Explain Plan
SELECT STATEMENT ALL_ROWS
Cost: 6 Bytes: 1,296 Cardinality: 2
3 COUNT STOPKEY
2 TABLE ACCESS BY INDEX ROWID TABLE people
Cost: 6 Bytes: 1,296 Cardinality: 2
1 INDEX RANGE SCAN INDEX people_IDX4
Cost: 3 Cardinality: 2

Answer 1

LIKE模式匹配条件希望将字符类型视为左侧和右侧操作数。遇到NUMBER时，会将其隐式转换为char。您的查询1基本上是无声地重写为：

SELECT a1.*
  FROM people a1
 WHERE TO_CHAR(a1.id) LIKE '119%'
   AND ROWNUM < 5

在您的情况下会发生这种情况，这很糟糕，原因有两个：

1. 每行都会执行一次转换，这很慢；
2. 由于WHERE谓词中的函数（尽管是隐式的），Oracle无法使用A1.ID列索引。

要解决此问题，您需要执行以下一项操作：

1. 在列上创建基于函数的索引A1.ID：

CREATE INDEX people_idx5 ON people (TO_CHAR(id));

1. 如果您需要匹配ID列的前3个字符的记录，请创建仅包含这3个字符的NUMBER类型的另一列，并在其上使用plain = 运算符。

2. 创建一个 单独ID_CHAR的类型列，VARCHAR2并用填充TO_CHAR(id)。将其编入索引并ID在您的WHERE情况下使用而不是。

当然，如果您选择基于现有ID列创建其他列，则需要使这2个列保持同步，您可以将其作为单个UPDATE进行批量处理，也可以在ON-
UPDATE触发器中进行处理，或者将该列添加到适当的位置代码中的INSERT和UPDATE语句。