sql-serveroracle-databasefiltered-index

Does Oracle have a filtered index concept?


Similar to SQLServer where I can do the following

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

Solution

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