Oracle 10g - 优化WHERE IS NOT NULL

我们有Oracle 10g,我们需要查询1个表(没有连接)并过滤掉其中1个列为空的行.当我们这样做 - WHERE OurColumn不是NULL - 我们在一张非常大的桌子上得到一个全表扫描 - BAD BAD BAD.该列上有一个索引但在此实例中会被忽略.这有什么解决方案吗？

谢谢

1> Quassnoi..：

---

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

如果只有几NULL行,则优化器是正确的.

如果您完全确定索引访问速度会更快(也就是说,您有多个75%行col1 IS NULL),则提示您的查询:

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

为什么75%？

因为使用INDEX SCAN检索索引未涵盖的值意味着隐藏连接ROWID,所以花费的4时间大约是表扫描的倍数.

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

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

---

可空列上的"IS NOT NULL"使得此列上的索引有资格包含在计划中,Oracle对此非常聪明:)

---

在全表扫描中,您只需遍历表中的所有行; 如果进行索引扫描,首先必须读取索引,然后读取表.从某一点来看,阅读指数的成本高于简单阅读整个表格.

2> Tony Andrews..：

---

优化器将根据全表扫描的相对成本和使用索引做出决策.这主要归结为必须读取多少块以满足查询.在另一个答案中提到的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%的行具有非null 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行相比),并建议使用全表扫描.请参阅此DBAZine有关聚类因子的文章.