通过复合索引来避免is null使索引失效！

最新推荐文章于 2024-07-30 04:02:08 发布

事后诸葛亮

最新推荐文章于 2024-07-30 04:02:08 发布

阅读量4.4k

点赞数

分类专栏： index 文章标签： null sql object access table disk

本文链接：https://blog.csdn.net/zq9017197/article/details/7089332

版权

index 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

由于oracle索引的特性，使得is null这种条件的sql不能够使用索引。但是我们可以通过复合索引的形式来使这种sql也能使用索引，下面是一个简单的测试：

SQL> create table t as select * from dba_objects;

表已创建。

SQL> create index idx_t_id1 on t(object_id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly;
SQL> select * from t where object_id is null;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    97 |   287   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    97 |   287   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1030  consistent gets
          0  physical reads
          0  redo size
       1184  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autotrace off;
SQL> create index idx_t_id2 on t(object_id,1);

索引已创建。

SQL> set autotrace traceonly;
SQL> select * from t where object_id is null;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 2286710094

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    97 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    97 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_ID2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID" IS NULL)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1184  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

在上面的测试中，建立了(object_id,1)这样一个复合索引，而常量“1”的存在，将会使表中的所有行都能够进行索引，这样使得如“select * from t where object_id is null”这样的sql也能够使用索引，这在null值非常少时，能够大幅度提升sql的性能。