1.建立測試表
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 * 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才可以走索引；

謂詞條件是is null走索引嗎？

相關文章