Does Oracle have a filtered index concept?

Question

Similar to SQLServer where I can do the following

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

Answer 1

Here's a small variant on Justin and Alex's answer that might save further index space and makes the modified query more readable IMO:

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

SELECT * FROM TimeSeriesPeriod
  WHERE 1 = (CASE WHEN completionStatus = 'Complete' AND validationStatus = 'Pending'
                THEN 1
                ELSE NULL
             END)

Answer 2

You might be able to use a function-based index for this, though it isn't very pleasant for this scenario:

create index TimeSeriesPeriodSs1 on TimeSeriesPeriod (
    case when validationStatus= N'Pending' and completionStatus= N'Complete' then validationStatus else null end,
    case when validationStatus= N'Pending' and completionStatus= N'Complete' then completionStatus else null end);

You'd have to make the query's where clause match exactly to make it use the index though.

select <fields>
from TimeSeriesPeriod
where case when validationStatus= N'Pending' and completionStatus= N'Complete' then validationStatus else null end = N'Pending'
and case when validationStatus= N'Pending' and completionStatus= N'Complete' then completionStatus else null end = N'Complete';

This would be a lot neater if you can define (deterministic) functions to do the case. See here for some further info and examples. Or this, from a quick Google.

Answer 3

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