Oracle中VARCHAR字段按数值比较且利用索引的方案问询

咱们先聊第一个问题：执行计划显示CBO可以用索引扫描，这个情况真实吗？

答案是有可能真实，但有前提条件。你现在用的是字符串范围比较，而字段是VARCHAR类型且有索引。如果你的ACCARDACNT字段里的所有值都是固定长度的数字字符串（比如例子里的16位，前面补零对齐），那字符串的字典序比较结果和数值比较结果是完全一致的——这时候CBO确实会选择索引扫描，因为索引是按字符串字典序排序的，范围条件能直接匹配索引的有序性，查询结果也符合你的数值比较预期。

但如果字段里的字符串长度不固定，或者包含非数字字符，那字典序和数值序就会出现偏差（比如'0100'作为字符串比'99'小，但数值上100 > 99），这时候即使执行计划显示用了索引，查询结果也是错误的，而且CBO因为不知道你要的是数值比较，还是会优先选择索引扫描。

接下来聊聊核心问题：怎么实现按数值比较的同时还能利用索引？给你几个靠谱的方案：

1. 直接修改字段类型为数值型（最优解） #

既然这个字段本质是用来存储数值的，最彻底的办法就是把它改成NUMBER类型。这样数值比较是天然支持的，原有的索引（或者重新建的数值型索引）能直接被CBO利用，完全不会有歧义。当然改字段类型要考虑业务影响，比如有没有程序依赖这个字段的字符串格式，需要提前协调测试，但长远来看这是最省心的方案。

2. 创建基于函数的索引（无法改字段类型时的首选） #

如果因为各种原因没法修改字段类型，那就创建一个基于TO_NUMBER函数的索引，这样既能实现数值比较，又能利用索引。

首先创建函数索引：

CREATE INDEX idx_accardacnt_to_number ON your_table (TO_NUMBER(ACCARDACNT));

然后查询的时候必须严格使用相同的函数来写条件，比如：

SELECT * FROM your_table a
WHERE TO_NUMBER(a.ACCARDACNT) > 880080200000006 
  AND TO_NUMBER(a.ACCARDACNT) < 880080200001000;

这样CBO就会选择这个函数索引来做范围扫描，同时保证了数值比较的正确性。注意要确保字段里的所有值都能正常转成数字，不然TO_NUMBER会抛出错误。如果存在无效值，可以用VALIDATE_CONVERSION先过滤：

SELECT * FROM your_table a
WHERE VALIDATE_CONVERSION(a.ACCARDACNT AS NUMBER) = 1
  AND TO_NUMBER(a.ACCARDACNT) > 880080200000006 
  AND TO_NUMBER(a.ACCARDACNT) < 880080200001000;

3. 依赖固定格式的字符串比较（临时 workaround） #

如果你的字段已经是固定长度的补零数字字符串（比如例子里的16位），那当前的字符串范围查询结果和数值比较是一致的，这时候用索引扫描没问题。但这是依赖于业务对字段格式的严格约定，一旦某天格式被打破（比如少补了一个零，或者出现非数字字符），查询结果就会出错，所以这只能作为临时过渡方案，不建议长期使用。

内容的提问来源于stack exchange，提问作者Mahsa ehsani