Oracle 10g-优化WHERE IS NOT NULL [英] Oracle 10g - optimize WHERE IS NOT NULL

问题描述

我们有Oracle 10g，我们需要查询1个表(无联接)并过滤出其中1列为空的行.当我们这样做时-WHERE OurColumn不为空-我们在非常大的表-BAD BAD BAD上进行了全表扫描.该列上有一个索引，但在此实例中将被忽略.有什么解决办法吗?

We have Oracle 10g and we need to query 1 table (no joins) and filter out rows where 1 of the columns is null. When we do this - WHERE OurColumn IS NOT NULL - we get a full table scan on a very large table - BAD BAD BAD. The column has an index on it but it gets ignored in this instance. Are there any solutions to this?

谢谢

推荐答案

优化器认为全表扫描会更好.

The optimizer thinks that the full table scan will be better.

如果只有NULL行，则说明优化器是正确的.

If there are just a few NULL rows, the optimizer is right.

如果您完全确定索引访问会更快(也就是说，具有col1 IS NULL的行超过75%行)，则提示您查询:

If you are absolutely sure that the index access will be faster (that is, you have more than 75% rows with col1 IS NULL), then hint your query:

SELECT  /*+ INDEX (t index_name_on_col1) */
        *
FROM    mytable t
WHERE   col1 IS NOT NULL

为什么75%?

由于使用INDEX SCAN来检索索引未涵盖的值，这意味着ROWID上存在隐藏的联接，其成本约为表扫描的4倍.

Because using INDEX SCAN to retrieve values not covered by the index implies a hidden join on ROWID, which costs about 4 times as much as table scan.

如果索引范围包含多于25%行，则表扫描通常会更快.

If the index range includes more than 25% of rows, the table scan is usually faster.

如Tony Andrews所述，聚类因子是测量此值的更准确方法，但25%仍然是一个很好的经验法则.

As mentioned by Tony Andrews, clustering factor is more accurate method to measure this value, but 25% is still a good rule of thumb.

这篇关于Oracle 10g-优化WHERE IS NOT NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！