为分区字段创建索引是否冗余？以start_date区间分区场景为例

你采用了基于start_date的区间分区方案，分区规则如下：

...PARTITION BY RANGE(start_date) INTERVAL(NUMTOYMININTERVAL(1, 'YEAR')...)

日常查询以start_date为过滤条件，典型语句如下：

SELECT columns
FROM My_Table
WHERE start_date BETWEEN SYSDATE-90 AND SYSDATE
ORDER BY start_date;

针对你的疑问，直接给出结论：为start_date建索引不是冗余操作，在你的场景下能带来明确的额外收益，具体分析如下：

• 分区与索引的核心作用不同
  分区的核心是数据裁剪：查询时数据库会自动跳过不符合start_date范围的分区，只扫描目标分区（比如你查最近90天，只会涉及当前年度的1个分区，最多加上上一年度的部分数据），但分区本身无法优化分区内的数据定位和排序。而索引的作用是快速定位分区内的目标数据，两者是互补而非替代关系。

• 你的场景下的具体收益

  1. 降低分区内扫描成本：即使经过分区裁剪，目标分区可能仍包含数百万甚至上千万条数据。如果没有索引，数据库需要全量扫描整个分区才能筛选出最近90天的数据；有了start_date索引后，可直接通过索引定位到时间范围内的行，大幅减少IO开销。
  2. 避免额外排序操作：你的查询要求按start_date排序，若使用B树索引（默认类型），索引本身是有序的，数据库可以直接通过索引顺序读取数据，省去了对结果集的排序步骤，提升查询效率。
  3. 适配未来数据增长：当前按年分区，单个分区数据量可能可控，但随着业务发展，年度分区内的数据量会持续膨胀，届时索引的性能优势会更加明显。

• 特殊场景下可考虑不建索引
  仅当单个分区的数据量极小（比如单分区只有几千条数据），全扫描的成本远低于索引维护开销（插入/更新/删除时的索引写入）时，才可以考虑不建索引。但这种情况在你提到的“大型数据表”场景中几乎不存在。

• 推荐使用本地分区索引
  针对分区表，优先创建本地分区索引（Local Partitioned Index），它与表的分区策略完全对齐，每个表分区对应一个独立的索引分区。这种索引在执行分区维护（比如删除过期分区）时会自动同步更新，不会出现全局索引失效的问题，同时能更好地配合分区裁剪发挥性能优势。

内容的提问来源于stack exchange，提问作者Jeromy French