受楼下老大的NULL贴启发，讲两个NULL与索引的小技巧

newkid · 发表于 2008-7-12 03:40

1.既然NULL是可以进复合索引的，在我们需要对NULL进行索引时，就可以构造一个“伪复合索引”：

CREATE INDEX my_index ON my_table(my_column,0);

后面这个零就是加入的伪列。这样以后在有 my_column IS NULL 的条件就可以利用索引了（当然最终使用与否还得由CBO决定）。

2.不想索引的行，即使不是NULL, 也可用函数把它剔除。
  假设有status_id列，里面有0:未处理,1:已处理两种状态，我们关心的仅仅是0的行，处理完就会改成1. 这样表中0的行仅仅是少数，大部分是1的行，数据量多了BTREE索引的维护就有开销。
  这时可以建立这样的索引：
  CREATE INDEX my_index ON my_table(DECODE(status_id,0,0));
  它只对0行数据进行索引。当你要取未处理数据时，SELECT * FROM my_table WHERE DECODE(status_id,0,0)=0 就可以高效利用索引。

newkid · 发表于 2008-7-12 03:53

另有一个陷阱要小心避免：
SELECT ... FROM t1 WHERE my_column NOT IN (SELECT my_column FROM t2);

假如这个子查询SELECT my_column FROM t2里面有NULL, 那么主查询就一行数据也出不来了! 必须改用:
SELECT ... FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.my_column = t1.my_column);

zhangweicai74 · 发表于 2008-7-12 08:07

原帖由 newkid 于 2008-7-12 03:53 发表
另有一个陷阱要小心避免：
SELECT ... FROM t1 WHERE my_column NOT IN (SELECT my_column FROM t2);

假如这个子查询SELECT my_column FROM t2里面有NULL, 那么主查询就一行数据也出不来了! 必须改用:
SELECT ... FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.my_column = t1.my_column);

支持,这个真还要小心!!

chenk818 · 发表于 2008-7-12 08:27

原帖由 newkid 于 2008-7-12 03:53 发表
另有一个陷阱要小心避免：
SELECT ... FROM t1 WHERE my_column NOT IN (SELECT my_column FROM t2);

假如这个子查询SELECT my_column FROM t2里面有NULL, 那么主查询就一行数据也出不来了! 必须改用:
SELECT ... FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.my_column = t1.my_column);

问一个，为什么查不出了？
另外，楼主说的第二种情况，用位图索引是否更好些?