ITPub博客

首页 > 数据库 > Oracle > 谓词条件是is null走索引吗?

谓词条件是is null走索引吗?

原创 Oracle 作者:parknkjun 时间:2014-12-23 23:59:18 0 删除 编辑
1.创建测试表
JZH@TEST>create table t (x int,y int);
Table created.
JZH@TEST>create index t_indx on t(x,y);
Index created.
JZH@TEST>insert into t values(1,1);
1 row created.
JZH@TEST>insert into t values(1,null);
1 row created.
JZH@TEST>insert into t values(null,1);
1 row created.
JZH@TEST>insert into t values(null,null);
1 row created.
JZH@TEST>commit;
Commit complete.
2.分析索引
JZH@TEST>analyze index t_indx validate structure;
Index analyzed.
3.查看数据
JZH@TEST>select count(*) from t;
 COUNT(*)
----------
         4
JZH@TEST>select name,lf_rows from index_stats;
NAME                              LF_ROWS
------------------------------ ----------
T_INDX                                  3
表中有4行数据,而索引只存储了3行,还有一行null,null索引是不存储的,因此select * from t where x is null是不走索引的,接下来看一下:
JZH@TEST>set autot traceonly
JZH@TEST>select * from t where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    52 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |    52 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("X" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        636  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
可以看到oracle选择了走全表扫描,只有索引列中至少有一列为not null,oracle才会选择走过引,下面将y列修改为not null,再看执行计划;
JZH@TEST>alter table T modify y not null;
alter table T modify y not null
*
ERROR at line 1:
ORA-02296: cannot enable (JZH.) - null values found
JZH@TEST>delete from t where y is null;
2 rows deleted.
JZH@TEST>commit;
Commit complete.
JZH@TEST>alter table T modify y not null;
Table altered.
因为Y列有null值,所以不让修改,删除2行null值,再修改;
再看select * from t where x is null的执行计划;
JZH@TEST>begin
  2  dbms_stats.gather_table_stats(user,'T');
  3  end;
  4  /
PL/SQL procedure successfully completed.
JZH@TEST>select * from t where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 4058602070
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_INDX |     1 |     5 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X" IS NULL)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        583  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
总结:
1、谓词是is null的也可以使用B树索引;
2、如果索引前导列是X,那索引其他列必须至少有一列是not null才可以走索引;







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10271187/viewspace-1377914/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
DBA攻城狮

注册时间:2008-02-11

  • 博文量
    115
  • 访问量
    519556