Oracle是否有筛选索引概念? [英] Does Oracle have a filtered index concept?

问题描述

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

Similar to SQLServer where I can do the following

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

推荐答案

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

You can create a function-based index in Oracle that leverages the fact that NULL values aren't stored in b-tree indexes. Something like

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)
       );

这篇关于Oracle是否有筛选索引概念?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！