"SQL优化中避免在索引列上使用函数，否则可能引发优化器选择全表扫描。但有些例外情况，例如特定条件下的非空约束，即使使用函数，优化器仍会选择使用索引。"
在SQL查询优化中，一个重要的原则是避免在索引列上使用函数或运算，因为这可能导致Oracle优化器放弃使用索引，转而执行全表扫描，从而降低查询性能。然而，这一原则并非绝对，某些特定情况下，Oracle优化器能够处理带有函数的索引，尤其是在有非空约束的情况下。
1. **函数与索引**: 当对索引列进行函数操作，如`substr()`或`trunc()`，优化器通常会认为无法利用索引。例如，`substr(hbs_bh，1，4)=’5400’` 可以优化为 `hbs_bh like '5400%'`，这样就能利用索引进行范围查找。
2. **运算与索引**: 显式或隐式的运算也会导致索引失效，比如 `ss_df+20>50` 可以转化为 `ss_df>30`，以利用索引。同样，对于字符串连接 `'X'||hbs_bh>’X5400021452’` 可优化为 `hbs_bh>'5400021542'`。
3. **多字段运算与索引**: 当条件包含多个表字段的运算，如 `ys_df>cx_df` 或 `qc_bh||kh_bh='5400250000'`，通常不能使用索引，除非将这些运算拆分成单独的索引字段比较。
4. **隐式类型转换**: 当比较不同类型的数据，如字符型字段与数字比较 `hbs_bh=5401002554`，Oracle会进行隐式类型转换，导致索引失效。这种情况下，应显式转换类型，如 `hbs_bh='5401002554'`。
然而，存在一些例外情况，当查询只选择索引列，并且索引列有非空约束时，即使使用函数，优化器仍可能使用索引。例如，尽管 `UPPER(t.object_name)='T'` 不能直接利用索引，但添加非空约束 `t.object_name IS NOT NULL` 后，如 `UPPER(t.object_name)='T' AND t.object_name IS NOT NULL`，优化器可以使用索引。
因此，在进行SQL优化时，理解这些特殊情况至关重要，以确保既能利用索引提高查询效率，又能避免全表扫描带来的性能损失。在编写查询时，应尽可能地避免函数和运算对索引的影响，或者通过重构查询来适应Oracle优化器的行为。同时，定期分析和审查查询执行计划，以便发现并解决潜在的性能瓶颈。
我的内容管理
展开
