HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
TBD STATUS indexing in Oracle
ORACLE STUFFS
08-Mar-2009
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)*/.

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky