null与索引

JEL@JEL >create table t (x int,y int);

Table created.

JEL@JEL >create unique index i_t on t(x,y);

Index created.

JEL@JEL >insert into t values (1,1);

1 row created.
JEL@JEL >insert into t values (1,null);

1 row created.

JEL@JEL >insert into t values (null,1);

1 row created.

JEL@JEL >insert into t values (null,null);

1 row created.

JEL@JEL >insert into t values (null,1);
insert into t values (null,1)
*
ERROR at line 1:
ORA-00001: unique constraint (JEL.I_T) violated

JEL@JEL >insert into t values (null,null);

1 row created.

如上知，（null,1）与（null,1）是相同的，而（null,null）与（null,null）不同

JEL@JEL >set autotrace on
JEL@JEL >select * from t where x is null;

         X          Y
---------- ----------
                    1




Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |     9 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     3 |     9 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X" IS NULL)

如上计划执行全表扫描


对列y加not null约束
JEL@JEL >truncate table t;

Table truncated.

JEL@JEL >alter table t modify y int not null;

Table altered.

JEL@JEL >insert into t values (1,1);

1 row created.

JEL@JEL >insert into t values (null,1);

1 row created.

JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T');

PL/SQL procedure successfully completed.

JEL@JEL >set autotrace on
JEL@JEL >select * from t where x is null;

         X          Y
---------- ----------
                    1


Execution Plan
----------------------------------------------------------
Plan hash value: 2616361825

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T  |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X" IS NULL)

如上走索引了


结论，对于复合索引，只有当索引键中至少一个列定义为not null时，查询才会使用索引

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1063527/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1063527/