sql-server - Oracle是否有筛选索引概念？

与SQLServer类似，我可以执行以下操作

create index TimeSeriesPeriodSs1 on TimeSeriesPeriod (validationStatus, completionStatus)
where completionStatus= N'Complete'
and  validationStatus= N'Pending'

最佳答案

您可以在Oracle中创建一个基于函数的索引，该索引利用了NULL值未存储在b树索引中的事实。就像是

CREATE INDEX TimeSeriesPeriodSs1
    ON TimeSeriesPeriod(
          (CASE WHEN completionStatus = 'Complete' AND validationStatus = 'Pending'
                THEN validationStatus
                ELSE NULL
            END),
          (CASE WHEN completionStatus = 'Complete' AND validationStatus = 'Pending'
                THEN completionStatus
                ELSE NULL
            END)
       );