In Oracle 11g, say, I have a table Task which has a column ProcessState. The values of this column can be Queued, Running and Complete (can have couple more states in future). The table will have 50M+ data with 99.9% of rows having Complete as that column value. Only a few thousand rows will have value Queued/Running.
I read that although bitmap index is good for low cardinality column, but that is used largely for static tables.
So, what index can improve the query for Queued/Running tasks? bitmap or normal non-unique b-tree index?
Also, what index can improve the query for a binary column (NUMBER(1,0) with just yes/no values) ?
Disclaimer: I am an accidental dba.
A regular (b*tree) index is fine. Just make sure there is a histogram on the column. (See METHOD_OPT parameter in DBMS_STATS.GATHER_TABLE_STATS).
With a histogram on that column, Oracle will have the data it needs to make sure it uses the index when looking for queued/running jobs but use a full table scan when looking for completed job.
Do NOT use a bitmap index, as suggested in the comments. With lots of updates, you'll have concurrency and, worse, deadlocking issues.
Also, what index can improve the query for a binary column (NUMBER(1,0) with just yes/no values)
Sorry -- I missed this part of your question. If the data in the column is skewed (i.e., almost all 1 or almost all 0), then a regular (b*tree) index as above. If the data is evenly distributed, then no index will help. Reading 50% of your table's rows via an index will be slower than a full table scan.