Does Oracle have a filtered index concept?
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)
);
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.