oracle null值应用索引

来源：互联网发布：淘宝alexa 编辑：程序博客网时间：2024/06/05 03:37

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列空值查询使用索引的效果