How to index Y/N column in Oracle

I have a large table (6m records) containing data licensed from a vendor. The table contains an NVARCHAR2(1) column with Y/N values. I have created a view to filter out records with a value of 'N', and开发者_JS百科 this view will be queried extensively. What is the best way to index the NVARCHAR2(1) column?

What is the Y/N ratio ? Are records updated so they go from Y/N and/or N/Y ? If so, does this happen regularly (eg in-stock/out-of-stock/in-stock/out-of-stock) or as a one off (unprocessed/processed) ?

If the entries are mixed around, and you've got an even-ish ratio then an index is unlikely to help.

You could use partitioning (if you have the licence) to split Y from N. Materialized views or making the table a UNION ALL view over two tables (maybe with INSTEAD OF triggers) could split Y from N too. All these will incur at a penalty to update processing.

A bitmap index may be appropriate if the column doesn't get much update activity.

You could have a function based index on CASE WHEN flag = 'Y' then 'Y' end. That would exclude N values from the index making it a lot smaller.

Use a bitmapped index.

http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm

If the table has a large skew on the Y/N values, you may need to look into Histograms as well:

http://www.dba-oracle.com/t_histograms.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i42219

Maybe use a materialized view? Then all your queries will be operating on the smaller 'Y'-only table.