非操作符

 网上有些地方说有关非的操作符不能利用索引，这些操作符有 != 、 <> 、 !< 、 !> 、 NOT EXISTS 、 NOT IN 、 NOT LIKE 。 下面来测试一下 != 、 <> 、 !< 、 !> 、 NOT IN （ NOT EXISTS 、 NOT LIKE 会在其他文章中详细说明） 。

测试版本为 DB2 v8.1.0.64 ( 查看 DB2 版本 db2level) 和 DB2 v9.7

1. 测试选择性不强的情况。

先创建测试数据 :

create table student
(
    id int primary key not null ,
    sid   int
)

注 : 定义主键时系统自动创建索引，如果 DROP 表，关于这个表的所有索引也被删除，也包括系统创建的索引。

create procedure insertDate()
BEGIN
     DECLARE v_id int;
     set v_id = 0;
     while v_id < 100000
     DO
       insert into student values(v_id,v_id );
       set v_id = v_id + 1;
     end while;
END ;

-- 插入数据

         call insertDate ()

-- 创建索引

         CREATE INDEX STU_SID ON STUDENT
    ( SID    ASC
    )
    PCTFREE 10
    ALLOW REVERSE SCANS ;

注意 : 在一个表的数据大量修改后，要运行下面的命令：

RUNSTATS ON TABLE NBADV. STUDENT FOR INDEX  NBADV. STU_SID   SHRLEVEL REFERENCE

runstats on table nbadv . student with distribution and detailed indexes all

注 :NBADV. STUDENT 是 [ 模式名 ].[ 表名 ] NBADV. STU_SID 是 [ 模式名 ].[ 表名 ]

测试语句 :

select sid from student where sid <> 1 (!= 和 <> 一样，就不测试了 )

 

select sid from student where sid !< 1

 

  

select sid from student where sid !> 100001

  

 

 

上面的语句都只能排除 10W 分之一，可以看到都没有利用索引。

 

2. 测试选择性强的情况。

 

  将上面的测试数据执行下面的修改

update student set sid = 1 where sid > 100 or sid = 0

注意 : 在一个表的数据大量修改后，要运行下面的命令：

RUNSTATS ON TABLE NBADV. STUDENT FOR INDEX  NBADV. STU_SID   SHRLEVEL REFERENCE

 

测试语句 :

select sid from student where sid <> 1 (!= 和 <> 一样，就不测试了 )

 

 

select sid from student where sid !< 2

 

 select sid from student where sid > 1 or sid < 1

 

 可以发现上面三个语句都利用了索引，但是 <> 利用索引后效率并没有高出多少，而 !< 利用索引后效率很高。将 select sid from student where sid <> 1 转化为 select sid from student where sid > 1 or sid < 1 后效率也是很高。我是这么理解的， <> 时将索引列上所有数据一个个取出来判断，然后取出不等于 1 的数据，这样索引列上每一个数据都要被取出判断，所以，效率很低。但是 !< 时，首先找到所以列上数据 2 的位置，然后将 2 之后的数据都取出来，由于查找 2 的位置很快，而取出数据也只有 100 个，所以，速度会很快。而 <1 and

>1 会查找 1 的开始位置和结束位置，然后取出开始位置之前和结束位置之后的数据，由于查找数据位置很快，而取出数据 也只有 100 行，所以速度很快。

   

下面测试 not in

当 10 万行数据中只有 100 不等于 1 的数据时：

select sid from student where sid   not in  (1)

 

 

select sid from student where sid > 1

 

  

当 10 万行数据中只有 99999 不等于 1 的数据时：

select sid from student where sid   not in  (1)

和

select sid from student where sid > 1

执行计划一样，如图：

 

 

 可以看到当条件的筛选性不强时， not in 和其他方式效率差不多，当数据筛选性很强时，就需要将 not in 转换成其他写法了。

 

总结 :

<> 虽和 not in 虽然能利用索引，但是利用索引后效率并不能改变多少，所以建议在数据筛选性很强时（满足条件的数据比较多），将 <> 转化为 < XX and >XX ， not in 也可以转化成别的。而 !< 或者 !> 可以利用索引，且索引利用后效率提升很多，不必转换。