informix 索引小实验

复合索引验证：

Test表中有三列：id,name,sum。 给id和name两列建立了一个复合索引：

Create index index_idname on test(id,name); //建立索引以后一定要进行统计更新操作，否则执行路径都不会用到索引！

实验一：

一：select * from  test where id = 40 and name = 'ben'//会用到索引路径执行路径如下：

select * from  test where id = 40 and name = 'ben'

Estimated Cost: 1

Estimated # of Rows Returned: 1

  1) informix.test: INDEX PATH

    (1) Index Keys: id name   (Serial, fragments: ALL)

        Lower Index Filter: (informix.test.id = 40 AND informix.test.name = 'ben' )

二：select * from  test where id = 40 //会用到索引路径。所以，建了复合索引index_idname(id,name)就不必要建立索引index_id(id).

三：select * from  test  where  name = 'ben' and id = 40 //更换name和id顺序也会利用索引路径

四：select * from  test where  name = 'ben' //使用顺序扫描而不会用到索引路径

五：select * from  test where  id = 40 and sum = 41//也会使用索引路径。执行路径中包括数据过滤和索引过滤：

select * from  test where  id = 40 and sum = 41

Estimated Cost: 1

Estimated # of Rows Returned: 1                                                 

  1) informix.test: INDEX PATH

        Filters: informix.test.sum = 41

    (1) Index Keys: id name   (Serial, fragments: ALL)

        Lower Index Filter: informix.test.id = 40

实验二

在上面复合索引index_idname(id,name)存在的情况下，继续添加一个索引index_id(id)，并做统计更新：

一：select * from  test where  id = 40执行路径如下：

select * from  test where  id = 40

Estimated Cost: 1

Estimated # of Rows Returned: 1

  1) informix.test: INDEX PATH

    (1) Index Keys: id   (Serial, fragments: ALL)

        Lower Index Filter: informix.test.id = 40

二：select * from  test where  id = 40 and name = 'ben'执行路径如下：

select * from  test where  id = 40 and name = 'ben'

Estimated Cost: 1

Estimated # of Rows Returned: 1

  1) informix.test: INDEX PATH

        Filters: informix.test.name = 'ben'

    (1) Index Keys: id   (Serial, fragments: ALL)

        Lower Index Filter: informix.test.id = 40这个执行路径中id使用的是索引路径，跟实验一中的第一个实验有所区别