暂无图片
暂无图片
2
暂无图片
暂无图片
1
暂无图片

Oracle-is null 使用索引

原创 谢辉元 2021-12-24
2246

因为索引是不包含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
复制
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

墨天轮福利君
暂无图片
3年前
评论
暂无图片 0
您好,您的文章已入选合格奖,10墨值奖励已经到账请查收! ❤️我们还会实时派发您的流量收益。
3年前
暂无图片 点赞
评论