如何索引Oracle中的Y / N列 [英] How to index Y/N column in Oracle

问题描述

我有一张大表（6米记录），其中包含从供应商处获得许可的数据。该表包含带有Y / N值的NVARCHAR2（1）列。我创建了一个视图来过滤掉值为'N'的记录，并且将广泛查询该视图。索引NVARCHAR2（1）列的最佳方法是什么？

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?

推荐答案

什么是Y / N比率？
记录是否更新，以便从Y / N和/或N / Y开始？
如果是这样，这是经常发生的（例如库存/缺货/库存/缺货）还是一次性（未加工/加工）？

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.

你可以使用分区（如果你有许可证）将Y从N中拆分。物化视图或使表成为两个表上的UNION ALL视图（可能带有INSTEAD OF触发器）也可以从N中拆分Y.所有这些将导致更新处理的惩罚。

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.

你可以在CASE WHEN标志='Y'然后'Y'结束时有一个基于函数的索引。这将从索引中排除N值，使其变得更小。

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.

这篇关于如何索引Oracle中的Y / N列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！