oracle null值应用索引
大家都知道,oracle不会空值null建立索引,因此在通过含有null值的条件检索数据时,将不会应用表上已有的索引。
但是有两种方法可以避免这样的情况
1、建立复合索引
2、建立nvl函数索引
先说第一种情况:建立复合索引
SQL> set autotrace traceonly;
--先建立一张测试表
SQL> create table test_f (id number,no number);
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into test_f values(i,i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
在表列no上建议一个索引
SQL> create index test_f_a on test_f(no);
Index created.
对表进行分析
SQL> exec dbMs_stats.gather_table_statS('SYS','TEST_F',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
现在用no列上附加条件no is null 查看执行计划:
SQL> select * from test_f where no is null;no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3687589707
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_F | 1 | 8 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NO" IS NULL)
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed
---发现尽管no列上有索引,也已经对表进行了分析,但是依然走的全表扫描。no is null不能应用该列上的索引。
---删除该索引,建立一个复合索引。SQL> drop index test_f_a;
Index dropped.
SQL> create index test_f_a on test_f(no,0);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TEST_F',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
--现在再执行刚才的查询,查看执行计划
SQL> select * from test_f where no is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 348613680
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_F | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_F_A | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NO" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--发现此时应用了该列上的所有,实现了索引范围扫描。
因此可以通过此方法 来解决在可能包含null值 和对该类有可能进行null值查询的列,建立此复合索引来在查询中使用索引提高效率
二:建立nvl函数索引
SQL> drop index test_f_a;
Index dropped.
SQL> create index test_f_a on test_f(nvl(no,0));
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TEST_F',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
--在对no列进行与null值查询的时候 使用nvl(no,0)查询。
SQL> select * from test_f where nvl(no,0)=0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 348613680
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_F | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_F_A | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("NO",0)=0)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed
--此时也能达到在no列空值查询使用索引的效果