Oracle 有过滤索引的概念吗？

Does Oracle have a filtered index concept?

提问by Mike Q

Similar to SQLServer where I can do the following

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

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

回答by Justin Cave

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

您可以在 Oracle 中创建基于函数的索引，以利用 NULL 值不存储在 b 树索引中的事实。就像是

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

回答by Alex Poole

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 whereclause match exactly to make it use the index though.

您必须使查询的where子句完全匹配才能使其使用索引。

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 herefor some further info and examples. Or this, from a quick Google.

如果您可以定义（确定性）函数来执行case. 有关更多信息和示例，请参见此处。或者这个，来自一个快速的谷歌。

回答by Dave Costa

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:

这是贾斯汀和亚历克斯回答的一个小变体，它可能会节省更多的索引空间并使修改后的查询在 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)

回答by Myron Yao

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