JZH@TEST>create table t (x int,y int);
Table created.
JZH@TEST>create index t_indx on t(x,y);
Index created.
JZH@TEST>insert into t values(1,1);
1 row created.
JZH@TEST>insert into t values(1,null);
1 row created.
JZH@TEST>insert into t values(null,1);
1 row created.
JZH@TEST>insert into t values(null,null);
1 row created.
JZH@TEST>commit;
Commit complete.
2.分析索引
JZH@TEST>analyze index t_indx validate structure;
Index analyzed.
3.查看数据
JZH@TEST>select count(*) from t;
COUNT(*)
----------
4
JZH@TEST>select name,lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_INDX 3
表中有4行数据，而索引只存储了3行，还有一行null,null索引是不存储的，因此select * from t where x is null是不走索引的，接下来看一下：
JZH@TEST>set autot traceonly
JZH@TEST>select * from t where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
可以看到oracle选择了走全表扫描，只有索引列中至少有一列为not null，oracle才会选择走过引，下面将y列修改为not null，再看执行计划；
JZH@TEST>alter table T modify y not null;
alter table T modify y not null
*
ERROR at line 1:
ORA-02296: cannot enable (JZH.) - null values found
JZH@TEST>delete from t where y is null;
2 rows deleted.
JZH@TEST>commit;
Commit complete.
JZH@TEST>alter table T modify y not null;
Table altered.
因为Y列有null值，所以不让修改，删除2行null值，再修改；
再看select * from t where x is null的执行计划；
JZH@TEST>begin
2 dbms_stats.gather_table_stats(user,'T');
3 end;
4 /
PL/SQL procedure successfully completed.
JZH@TEST>select count(*) from t;
COUNT(*)
----------
4
JZH@TEST>select name,lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_INDX 3
表中有4行数据，而索引只存储了3行，还有一行null,null索引是不存储的，因此select * from t where x is null是不走索引的，接下来看一下：
JZH@TEST>set autot traceonly
JZH@TEST>select * from t where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
可以看到oracle选择了走全表扫描，只有索引列中至少有一列为not null，oracle才会选择走过引，下面将y列修改为not null，再看执行计划；
JZH@TEST>alter table T modify y not null;
alter table T modify y not null
*
ERROR at line 1:
ORA-02296: cannot enable (JZH.) - null values found
JZH@TEST>delete from t where y is null;
2 rows deleted.
JZH@TEST>commit;
Commit complete.
JZH@TEST>alter table T modify y not null;
Table altered.
因为Y列有null值，所以不让修改，删除2行null值，再修改；
再看select * from t where x is null的执行计划；
JZH@TEST>begin
2 dbms_stats.gather_table_stats(user,'T');
3 end;
4 /
PL/SQL procedure successfully completed.
JZH@TEST>select * from t where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 4058602070
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_INDX | 1 | 5 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
总结：
1、谓词是is null的也可以使用B树索引；
2、如果索引前导列是X，那索引其他列必须至少有一列是not null才可以走索引；
Execution Plan
----------------------------------------------------------
Plan hash value: 4058602070
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_INDX | 1 | 5 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
总结：
1、谓词是is null的也可以使用B树索引；
2、如果索引前导列是X，那索引其他列必须至少有一列是not null才可以走索引；