Oracle 10g -优化WHERE IS NOT NULL

Question

我们有Oracle 10g，我们需要查询1个表(无联接)，并过滤掉其中1列为null的行。当我们这样做时-其中OurColumn不是NULL -我们得到一个非常大的表的全表扫描-坏的。该列有一个索引，但在这种情况下会被忽略。对此有什么解决方案吗？

谢谢

Answer 1

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

如果只有几个NULL行，那么优化器是正确的。

如果您绝对确定索引访问会更快(即，使用col1 IS NULL时您有更多的75%行)，那么提示您的查询：

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

为什么选择75%

因为使用INDEX SCAN检索索引未涵盖的值意味着在ROWID上进行隐藏连接，其成本大约是表扫描的4倍。

如果索引范围包含的行数超过25%，则表扫描通常会更快。

正如Tony Andrews提到的，聚类因子是衡量该值的更准确的方法，但25%仍然是一个很好的经验法则。

Answer 2

优化器将根据全表扫描和使用索引的相对成本做出决定。这主要归结为必须读取多少块才能满足查询。另一个答案中提到的25%/75%的经验法则过于简单:在某些情况下，即使获得1%的行，全表扫描也是有意义的-也就是说，如果这些行恰好分布在许多块中。

例如，考虑下表：

SQL> create table t1 as select object_id, object_name from all_objects;

Table created.
SQL> alter table t1 modify object_id null;

Table altered.

SQL> update t1 set object_id = null
  2  where mod(object_id,100) != 0
  3  /

84558 rows updated.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select count(*) from t1 where object_id is not null;

  COUNT(*)
----------
       861    

如您所见，T1中只有大约1%的行具有非空object_id。但由于我构建表的方式，这861行将或多或少均匀地分布在表中。因此，查询：

select * from t1 where object_id is not null;

可能会访问T1中的几乎每个块来获取数据，即使优化器使用了索引。因此，省去索引，进行全表扫描是有意义的！

帮助识别这种情况的一个关键统计数据是索引聚类因子：

SQL> select clustering_factor from user_indexes where index_name='T1_IDX';

CLUSTERING_FACTOR
-----------------
              460

这个值460非常高(与索引中的861行相比)，并且建议使用全表扫描。参见this DBAZine article on clustering factors。

Answer 3

如果您正在执行select *，那么进行表扫描而不是使用索引将是有意义的。如果您知道您感兴趣的列，那么您可以使用这些列加上正在应用IS NOT NULL条件的列来创建覆盖索引。