Oracle 10g-优化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

相关推荐