因为索引是不包含null字段的,所以当查询使用 is null作为条件时一般是使用不到索引(这里指B+树索引)而只有使用全表扫描了。但如查询的表特别大,is null的条件为必须,且没有其他好的选择条件,那么我们还是可以通过其他方式来创建一个复合索引,来将null 值也包含到索引中,而间接的让该查询也来走索引的。
可以创建的索引有下面几种:
1)普通复合索引 (col1,col2) col1 为查询条件为 is null的列, col2为有非空约束的列;
这种方式当查询条件 有 col1 is null 和 col2时会更高效,但索引相对更大。
2)带常数索引 (col1,‘1’) col1为查询条件 is null的列, 1为常量,也可是其他值。
只针对 col1 is null 适用。
下面来做个简单测试
创建测试表 test111;
HR@orcl>desc test111; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) --数据大小为100000 HR@orcl>select count(*) from test111; COUNT(*) ---------- 100000 --object_id is null 为1000 HR@orcl>select count(*) from test111 where object_id is null; COUNT(*) ---------- 10 HR@orcl>exec dbms_stats.gather_table_stats('HR','TEST111'); PL/SQL procedure successfully completed. --开启autotrace HR@orcl>set timing on; HR@orcl>set lines 200 HR@orcl>set autotrace traceonly; HR@orcl>alter session set statistics_level=all; Session altered. Elapsed: 00:00:00.04 --没有创建索引的情况下全表扫描,如下 HR@orcl>select * from test111 where object_id is null; 1000 rows selected. Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 3757802073 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 90000 | 379 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST111 | 1000 | 90000 | 379 (1)| 00:00:05 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1435 consistent gets 0 physical reads 0 redo size 86606 bytes sent via SQL*Net to client 1245 bytes received via SQL*Net from client 68 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000 rows processed HR@orcl>
复制
–分别创建如下复合索引:
1)IND_TEST111_1OBJID (‘1’, OBJECT_ID)
2) IND_TEST111_2OBJID (OBJECT_ID, ‘2’)
3) IND_TEST111_OBJIDOWNER (OBJECT_ID, OWNER)
4) IND_TEST111_OWNEROBJID (OWNER, OBJECT_ID)
查询各种执行计划开销如下,可以看到虽然不同索引之间性能虽有较大差别,但比起全表扫描都是大大提升了。
使用IND_TEST111_2OBJID(索引范围扫描)cost为12, 逻辑读为14 为最小;
使用IND_TEST111_OBJIDOWNER(索引范围扫描)cost为12, 逻辑读为16 ;
使用IND_TEST111_OWNEROBJID(索引跳跃扫描) cost为16, 逻辑读为21 ;
使用IND_TEST111_1OBJID(索引全扫描) cost为249, 逻辑读为248。
HR@orcl>select /*+ full(t)*/* from test111 t where object_id is null; 10 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3757802073 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 940 | 379 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST111 | 10 | 940 | 379 (1)| 00:00:05 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1370 consistent gets 0 physical reads 0 redo size 1793 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed HR@orcl>select /*+ index(t,IND_TEST111_OBJIDOWNER)*/* from test111 t where object_id is null; 10 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2382521718 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 940 | 12 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 12 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_TEST111_OBJIDOWNER | 10 | | 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 16 consistent gets 0 physical reads 0 redo size 1793 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed HR@orcl>select /*+ index(t,IND_TEST111_2OBJID)*/* from test111 t where object_id is null; 10 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2112287855 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 940 | 12 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 12 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_TEST111_2OBJID | 10 | | 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 14 consistent gets 0 physical reads 0 redo size 1793 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed HR@orcl>select /*+ index(t,IND_TEST111_OWNEROBJID)*/* from test111 t where object_id is null; 10 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 532671917 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 940 | 16 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 16 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | IND_TEST111_OWNEROBJID | 10 | | 6 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID" IS NULL) filter("OBJECT_ID" IS NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 21 consistent gets 0 physical reads 0 redo size 1793 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed HR@orcl>select /*+ index(t,IND_TEST111_1OBJID)*/* from test111 t where object_id is null; 10 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 235548458 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 940 | 249 (1)| 00:00:03 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 249 (1)| 00:00:03 | |* 2 | INDEX FULL SCAN | IND_TEST111_1OBJID | 10 | | 239 (1)| 00:00:03 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID" IS NULL) filter("OBJECT_ID" IS NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 248 consistent gets 0 physical reads 0 redo size 1793 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
复制
评论
