Oracle如何创建DT_SUBMIT非空的部分索引优化订单聚合查询？

方案1（推荐，适用于Oracle 12c及以上版本） #

直接使用Oracle原生支持的部分索引（Partial Index），仅对满足条件的行创建索引，语法如下：

CREATE INDEX BY_SUBMISSION_DATE_PARTIAL 
ON ORDERS (DT_SUBMIT, ID_DISCRIMINATOR, VL_TOTAL)
WHERE DT_SUBMIT IS NOT NULL;

优势：

• 自动过滤所有DT_SUBMIT为NULL的未提交订单，索引存储空间仅包含已提交订单数据，比原全量复合索引占用空间减少比例和未提交订单占比正相关
• 完全保留原复合索引的性能优势，原有查询不需要做任何改写，优化器可以自动匹配到该索引，无需回表即可完成所有查询计算
• 仅当修改DT_SUBMIT非空的行、或者将订单从未提交改为提交状态时才需要维护索引，DML操作开销大幅降低

方案2（兼容Oracle 11g及更早版本） #

利用Oracle B树索引不存储所有索引列全为NULL的行的特性，通过基于函数的索引模拟部分索引效果，语法如下：

CREATE INDEX BY_SUBMISSION_DATE_FUNC 
ON ORDERS (
    DT_SUBMIT,
    CASE WHEN DT_SUBMIT IS NOT NULL THEN ID_DISCRIMINATOR END,
    CASE WHEN DT_SUBMIT IS NOT NULL THEN VL_TOTAL END
);

该方案下，DT_SUBMIT为NULL时，后续两个函数列的值也为NULL，整个索引条目全为NULL不会被存入索引，达到和部分索引一致的空间节省效果。查询需要做少量改写适配索引：

SELECT 
    CASE WHEN DT_SUBMIT IS NOT NULL THEN ID_DISCRIMINATOR END ID_DISCRIMINATOR,
    SUM(CASE WHEN DT_SUBMIT IS NOT NULL THEN VL_TOTAL END) SUM_VL_TOTAL
FROM ORDERS
WHERE DT_SUBMIT IS NOT NULL
  AND DT_SUBMIT > :some_parameter
GROUP BY CASE WHEN DT_SUBMIT IS NOT NULL THEN ID_DISCRIMINATOR END;

也可以提前将CASE表达式创建为表的虚拟列，直接查询虚拟列即可简化SQL写法。

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