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'

Proboscidean

wouldn't you just create a normal index and put the where clause in your select SQL? That way your normal index would serve another select stmt with different where clause expression hitting the same fields. Not familiar with SQL Server filtered index though, so not clear of its benefit. – Spellbound

@Spellbound - Size could be a reason – Adey

@tbone: The whole point of a filtered index is to avoid the overhead of maintaining an index on a large table when only a small subset of it is needed. – Input

Doesn't Oracle need to "maintain" the entire index anyway? If we are talking about Oracle, and function based index is the answer for this, wouldn't any and all modified rows moving forward need to apply the function (even if the result is null and isn't stored, still all DML needs to apply the function to check). So maybe a bit faster on reads, but probably slower on DML, no? What do you really gain for this overhead (and unorthodox approach imo)? Just size of index? Not trying to be difficult (ok, maybe a bit;), but very curious – Spellbound

@Tbone Can be very much faster for reads. For example, you have millions or rows that have been processed, and a very small number that have not been processed. If you want to find the next ones to process, you don't want an index containing all those rows. – Ashwell

@Ashwell This is an old post, but I think my point was that maintaining an index (even a function based one with NULL pruning) has a cost. And the OP seems to have a specific logic that would probably make sense to put in the where clause of a select rather than a function based index. To use the function based index, you'd need the same CASE statement (confusing, and will have full scans unless the predicate syntax is correct) And soon after, someone else comes along with a need for a slightly different filter, and we have another index, etc... I would still opt for a plain ole b-tree index – Spellbound

Answer 1

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

Mariahmariam

alex poole answer's is better, as it suggests you to use a user defined function so to avoid having to write the same complex "case when" in your queries' where clause in order to allow oracle to use the index – Daemon

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.

Dextral

+1 for including where clause to use the function based index. This is a big difference in using SQL Server's filtered index versus Oracle's function based index. – Myrmeco

Answer 3

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)

Chace

Answer 4

A potential alternative/improvement on function-based indexes is to make use of virtual columns.

create table TimeSeriesPeriod (
  --...
  pendingValidation as (
    case when completionStatus = N'Complete' and validationStatus= N'Pending'
      then 1
    else null
  ) virtual
);
create index TimeSeriesPeriodSs1 on TimeSeriesPeriod (pendingValidation);

select * from TimeSeriesPeriod where pendingValidation = 1;

Note that statistics are collected for virtual columns/function-based indexes just like regular columns so they do have non-zero cost. Consider collapsing multiple filters into a single virtual column where possible

create table TimeSeriesPeriod (
  --...
  incompleteValidationStatus as (
    case when completionStatus = N'Complete' and validationStatus != N'Complete'
      then validationStatus
    else null
  ) virtual
);
create index TimeSeriesPeriodSs1 on TimeSeriesPeriod (incompleteValidationStatus);

select * from TimeSeriesPeriod where incompleteValidationStatus = N'Pending';
select * from TimeSeriesPeriod where incompleteValidationStatus = N'Failed Validation';

Heinrich