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 this view will be queried extensively. What is the best way to index the NVARCHAR2(1) column?
A:
Maybe use a materialized view? Then all your queries will be operating on the smaller 'Y'-only table.
Tony Andrews
2010-01-08 20:01:31
+1
A:
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
Dougman
2010-01-08 20:14:34
+5
A:
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.
Gary
2010-01-08 20:19:19
+1 Gary has covered most of the relevant issues - additionally, your typical queries probably also involve filtering on more selective columns and those will point you to the indexing you need.
dpbradley
2010-01-08 20:33:47
About 50/50 split between Y and N values. The data will never be "updated" per se, but we will replace the dataset wholesale as the vendor periodically distributes their latest stuff.
nw
2010-01-08 20:52:51