INDEX受到NULL值的影响

假设现在有如下一张表：

CREATE TABLE TBL(

    USERID NUMBER(20),

    NAME VARCHAR2(20),

    DEPTNO NUMBER(20)

)

;

--模拟数据

BEGIN

    FOR I IN 0..1000 LOOP

        INSERT INTO TBL VALUES(

        I,CONCAT('TBL',I),MOD(I,2));

    END LOOP;

END;

/

SELECT * FROM TBL;

在这张表的数据上面建立了如下的索引：

CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO);

在这张表上，修改了name和deptno两列为not null：

alter table tbl modify(name not null);

alter table tbl modify(deptno not null);

我们在这张表上执行如下查询：

ChenZw>  select name,deptno from tbl order by name,deptno;

已选择1001行。

已用时间:  00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 2449324302

------------------------------------------------------------------------------------------

| Id  | Operation        | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                       |  1001 |  8008 |     4   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | INDEX_TBL_NAME_DEPTNO |  1001 |  8008 |     4   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

我们看到，因为我们要查询的所有的数据都是在索引中可以提取，因此优化器选择了INDEX FULL SCAN的方式来获取数据，这里，如果没有排序操作的话，Oracle就会直接从索引中进行提取数据了，没有排序操作的INDEX FULL SCAN就是INDEX FAST FULL SCAN，如下面的执行计划：

ChenZw>  select name,deptno from tbl;

已选择1001行。

已用时间:  00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 3591538465

----------------------------------------------------------------------------------------------

| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                       |  1001 |  8008 |     3   (0)| 00:00:01 |

|   1 |  INDEX FAST FULL SCAN| INDEX_TBL_NAME_DEPTNO |  1001 |  8008 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

上面的结果就是没有排序，并且所有的数据都是可以直接从索引中获取的执行计划。

删除现在的表，然后再来一遍：

drop table tbl;

CREATE TABLE TBL(

    USERID NUMBER(20),

    NAME VARCHAR2(20),

    DEPTNO NUMBER(20)

)

;

--模拟数据

BEGIN

    FOR I IN 0..1000 LOOP

        INSERT INTO TBL VALUES(

        I,CONCAT('TBL',I),MOD(I,2));

    END LOOP;

END;

/

SELECT * FROM TBL;

CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO);

注意,在这里我们没有对name和deptno设置not null属性,我们看看现在的执行计划,为了保证这个表的索引是正确的,我们执行下面的语句,对表进行表分析:

analyze table tbl compute statistics for table for all indexed columns;

执行之后,执行一下语句,然后看一下执行计划:

ChenZw>  select name,deptno from tbl;

已选择1001行。

已用时间:  00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 2144214008

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1001 |  8008 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TBL  |  1001 |  8008 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

看到上面是没有用到索引的,然后看一下排序的结果呢?

ChenZw>  select name,deptno from tbl order by name,deptno;

已选择1001行。

已用时间:  00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 2469592408

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |  1001 |  8008 |     4  (25)| 00:00:01 |

|   1 |  SORT ORDER BY     |      |  1001 |  8008 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TBL  |  1001 |  8008 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

上面也没有用到索引!

这个是为什么呢?

因为如果索引项建立在属性为空的列,或者可能为空的列上,优化器是用不到这个索引的,这个也是为什么明明建立了索引,但是却没有产生效果的原因之一。

让我们来验证一下吧：

将name和deptno的列修改为非空属性的：

alter table tbl modify（name not null);

alter table tbl modify (deptno not null);

这个时候,继续执行上面的两个SQL看一下执行计划:

ChenZw>  select name,deptno from tbl;

已选择1001行。

已用时间:  00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 3591538465

----------------------------------------------------------------------------------------------

| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                       |  1001 |  8008 |     3   (0)| 00:00:01 |

|   1 |  INDEX FAST FULL SCAN| INDEX_TBL_NAME_DEPTNO |  1001 |  8008 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

和下面的这个SQL的结果:

ChenZw>  select name,deptno from tbl order by name,deptno;

已选择1001行。

已用时间:  00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 2449324302

------------------------------------------------------------------------------------------

| Id  | Operation        | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                       |  1001 |  8008 |     4   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | INDEX_TBL_NAME_DEPTNO |  1001 |  8008 |     4   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

现在可以看到，上面的执行计划中，已经正确的使用了索引了，因此上述给出的一个提议就是： 在建立索引来解决效率问题的时候，一定要检查一下建立索引的列应该是NOT NULL的。