原來對於索引的認識只知道索引可以基於一個或者多個列，B-Tree索引不包含null，但有些情況下我們又需要通過where 列名 is null來查找一些數據，這時候數據庫由於沒辦法使用索引就會使用全表掃描，導致執行效率低下，這時候我們可以通過使用含常數的復合索引來解決這個問題。
下面開始進行實驗：
首先建立測試表
SYS@ORCL>create table test_objects nologging as select rownum id,a.* from dba_objects a where 1=2;
Table created.
插入500萬條數據：
SYS@ORCL>declare
l_cnt number;
l_rows number:=&1;
begin
insert /*+ append */ into test_objects select rownum,a.* from dba_objects a;
l_cnt:=sql%rowcount;
commit;
while(l_cnt<l_rows)
loop
insert /*+ append */ into test_objects select rownum+l_cnt,
owner,object_name,subobject_name,
object_id,data_object_id,
object_type,created,last_ddl_time,
timestamp,status,temporary,
generated,secondary
from test_objects
where rownum<=l_rows-l_cnt;
l_cnt:=l_cnt+sql%rowcount;
commit;
end loop;
end; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 /
Enter value for 1: 5000000
old 3: l_rows number:=&1;
new 3: l_rows number:=5000000;
PL/SQL procedure successfully completed.
在object_id列上建立一般的B-Tree索引：
SYS@ORCL>create index idx_oid_test_objects on test_objects(object_id);
Index created.
收集表信息：
SYS@ORCL>exec dbms_stats.gather_table_stats('SYS','TEST_OBJECTS');
PL/SQL procedure successfully completed.
測試一下索引能否正常使用：
SYS@ORCL>select count(*) from test_objects where object_id=52457;
COUNT(*)
----------
99
Execution Plan
----------------------------------------------------------
Plan hash value: 3877533889
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | INDEX RANGE SCAN| IDX_OID_TEST_OBJECTS | 99 | 495 | 3 (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=52457)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到索引可以正常使用，下面我們來進入正題，當where條件中為object_id is null的時候會如何：
SYS@ORCL>select count(*) from test_objects where object_id is null;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3799704240
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 5 | 16612 (2)| 00:03:2
0 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 1 | 5 | 16612 (2)| 00:03:2
0 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
74808 consistent gets
74730 physical reads
904 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
因為B-Tree索引不會記錄null所以只能使用全表掃描。產生了大量的consistent gets下面我們來建立含常數的復合索引，並查詢執行：
SYS@ORCL>create index idx_oid2_test_objects on test_objects(object_id,1);
Index created.
SYS@ORCL>select count(*) from test_objects where object_id is null;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 1238205220
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | INDEX RANGE SCAN| IDX_OID2_TEST_OBJECTS | 1 | 5 | 2 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到此時擁有含場數的復合索引之後就能夠使用該索引查找null，極大的提升了執行效率。consistent gets從74808降到了3.效果非常明顯。
