It is not about a special type of index. That is just a technique of indexing columns indicating status TO BE DONE. What is common for such cases:
One or several statuses marking few TO BE PROCESSED records.
Majority of completed records.
Update of field is supposed.
What type of indexing of the field is the best?
B-tree indexes contraindications:
low cardinality, optimizer could not use the index
long time of indexing
Bitmap indexes contraindications:
massive updates on status columns - important contraindiocation of bitmap index usage
TBD status index
Following technique allows us to index selective important values of a field. We can utilize the fact NULL values are not indexes using B-tree index. Therefore when we index minority of rows, index is small and quickly mantainable. There are three possibilities how to deal with it:
Create specific column with TBD meaning, where unimportant majority has a NULL value.
Create functional index extracting TBD meaning, where unimportant majority is converted to NULL value.
Create virtual column (Oracle 11g) extracting TBD meaning, where unimportant majority is converted to NULL value and index it.(It is just an alternative to previous one)
Example:
Create index TAB1_TBD_IDX on TAB1(case when status='TBD' then 'TBD' else null end);
Another example:
Alter table TAB1 add TBD as (case when status='TBD' then 'TBD' else null end) VIRTUAL;
Create index TAB1_TBD_IDX of TAB1(TBD);
Unfortunatelly you have to modify queries to assure the index usage:
Change condition to use either a virtuual column or an indexed function.
Inclide hint to use an index /*+INDEX(TAB1 TAB1_TBD_IDX)*/.