通过案例学调优之--Oracle中null使用索引

最新推荐文章于 2023-11-17 18:13:57 发布
最新推荐文章于 2023-11-17 18:13:57 发布
阅读量767 收藏 1
点赞数 1
分类专栏： 数据库技术
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lqx0405/article/details/44777629
版权

通过案例学调优之--Oracle中null使用索引

     默认情况下，Oracle数据库，null在Index上是不被存储的，当在索引列以“is null”的方式访问时，无法使用索引；本案例，主要向大家演示如何在存在null的索引列上，使用“is null”访问索引。


案例分析：


1、建立表和普通索引

13:52:23 SCOTT@ prod >create table t2 (x int,y int);
Table created.
14:00:11 SCOTT@ prod >insert into t2 values (1,1);
1 row created.
Elapsed: 00:00:00.04
14:00:21 SCOTT@ prod >insert into t2 values (1,null);
1 row created.
Elapsed: 00:00:00.00
14:00:31 SCOTT@ prod >insert into t2 values (null,1);
1 row created.
Elapsed: 00:00:00.00
14:00:37 SCOTT@ prod >insert into t2 values (null,null);
1 row created.
Elapsed: 00:00:00.00
14:00:44 SCOTT@ prod >commit;
Commit complete.
Elapsed: 00:00:00.04
14:06:41 SCOTT@ prod >select * from t2;
         X          Y
---------- ----------
         1          1
         1
                    1
                    
14:36:12 SCOTT@ prod >create index t2_ind on t2(x);
Index created.

14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';
INDEX_NAME                     TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND                         T2                                      3

  在索引中只有3行，在最后一行字段全为null值，没有被存储！

14:36:27 SCOTT@ prod >exec dbms_stats.gather_index_stats(user,'T2_IND');
PL/SQL procedure successfully completed.

14:37:29 SCOTT@ prod >select * from t2 where x=1;
         X          Y
---------- ----------
         1          1
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 1173409066
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_IND |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
14:37:45 SCOTT@ prod >select * from t2 where x is not null;
         X          Y
---------- ----------
         1          1
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 463061910
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T2_IND |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X" IS NOT NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
14:38:00 SCOTT@ prod >select * from t2 where x is null;
         X          Y
---------- ----------
                    1

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     2 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("X" IS NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        508  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
当x通过“is null”访问时，Oracle选择了“full table scan”方式。

2、通过建立常量复合索引

14:38:55 SCOTT@ prod >create index t2_ind on t2(x,0);
Index created.

14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';
INDEX_NAME                     TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND                         T2                                      4

   索引块上存储了表中所用的行。

14:39:50 SCOTT@ prod >select * from t2 where x is null;
         X          Y
---------- ----------
                    1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1173409066
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_IND |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X" IS NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        508  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
对于x通过“is null”访问时，也能通过索引访问了！

3、建立复合索引（其他列为null）

13:59:40 SCOTT@ prod >create index x_ind on t2(x,y);
Index created.

14:08:29 SCOTT@ prod >EXEC dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T2');
PL/SQL procedure successfully completed.

14:09:22 SCOTT@ prod >EXEC dbms_stats.gather_index_stats(ownname=>USER,indname=>'X_IND');
PL/SQL procedure successfully completed.

14:09:58 SCOTT@ prod >select index_name,num_rows from user_indexes where index_name='X_IND';
INDEX_NAME                       NUM_ROWS
------------------------------ ----------
X_IND                                   3

14:10:50 SCOTT@ prod >select count(*) from t2;
  COUNT(*)
----------
         4
         
14:11:28 SCOTT@ prod >set autotrace on
14:12:33 SCOTT@ prod >select * from t2 where x=1;
         X          Y
---------- ----------
         1          1
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 3708139238
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     2 |     8 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| X_IND |     2 |     8 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        512  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
14:12:47 SCOTT@ prod >select * from t2 where x is not null;
         X          Y
---------- ----------
         1          1
         1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3776680409
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     2 |     8 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | X_IND |     2 |     8 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("X" IS NOT NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        512  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
14:13:08 SCOTT@ prod >select * from t2 where x is null;
         X          Y
---------- ----------
                    1
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     2 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("X" IS NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        508  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
 如果，复合索引列其他列也为null，在查询使用’is null‘条件时，仍然为“full table scan”。
          
14:13:52 SCOTT@ prod >select * from t2 where x=1 and y is null;
         X          Y
---------- ----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 3708139238
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| X_IND |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X"=1 AND "Y" IS NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        471  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
14:16:16 SCOTT@ prod >select * from t2 where x is null and y=1;
         X          Y
---------- ----------
                    1

Execution Plan
----------------------------------------------------------
Plan hash value: 3708139238
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| X_IND |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X" IS NULL AND "Y"=1)
       filter("Y"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        471  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

4、建立复合索引（其他列为 not null）


15:13:38 SCOTT@ prod >desc t2;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 X                                                                          NUMBER(38)
 Y                                                                          NUMBER(38)
 
15:13:43 SCOTT@ prod >alter table t2 modify (y NUMBER(38) not null);
Table altered.

15:14:01 SCOTT@ prod >desc t2;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 X                                                                          NUMBER(38)
 Y                                                                 NOT NULL NUMBER(38)
 
15:12:54 SCOTT@ prod >insert into t2 values (1,1);
1 row created.
Elapsed: 00:00:00.02
15:13:02 SCOTT@ prod >insert into t2 values (null,1);
1 row created.
Elapsed: 00:00:00.00
15:13:12 SCOTT@ prod >insert into t2 values (null,2);
1 row created.
Elapsed: 00:00:00.00
15:13:36 SCOTT@ prod >commit;
Commit complete.

15:15:00 SCOTT@ prod >create index t2_ind on t2 (x,y);
Index created.

15:15:29 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);
PL/SQL procedure successfully completed.

15:16:09 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';
INDEX_NAME                     TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND                         T2                                      3

15:17:20 SCOTT@ prod >set autotrace trace
15:17:26 SCOTT@ prod >SELECT * from t2 where x is null
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2876512201
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     2 |    10 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T2_IND |     2 |    10 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X" IS NULL)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        510  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
在复合索引中，如果其他列为not null，则在“is null”条件下，仍然可以使用索引访问。

结论：

对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。



本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1582469

客居天涯
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle索引失效的总结
08-17
oracle索引失效的总结
利用python-oracledb库连接Oracledb数据库，使用示例
06-24
python-oracledb的源码和使用示例代码， python-oracledb 1.0，适用于Python versions 3.6 through 3.10. Oracle Database； This directory contains samples for python-oracledb. 1. The schemas and SQL objects that are referenced in the samples can be created by running the Python script [create_schema.py][1]. The script requires SYSDBA privileges and will prompt for these credentials as well as the names of the schemas and edition that will be created, unless a number of environment variables are set
oracle isnull使用索引,isnull（）用法总结
weixin_39814925的博客
04-11 5812
SQL Serve中的isnull()函数：isnull(value1,value2)1、value1与value2的数据类型必须一致。2、如果value1的值不为null，结果返回value1。3、如果value1为null，结果返回vaule2的值。vaule2是你设定的值。如果在select中就是isnull可以判断是否是null，如果是给个默认值，isnull("字段名"，"设定默认的数据...
Oracle中NULL值与索引
weixin_33984032的博客
09-14 393
NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。由于NULL存在着无数的可能，因此NULL值也不等于NULL值，所以与NULL值相关的操作同样都为NULL值。正是基于这样一个特性，对于NULL值列上的B树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索...
oracle序列的空值,浅谈索引序列之是否可以存储NULL值？
weixin_39906358的博客
04-02 613
Oracle索引能否存储NULL值？询问周围的朋友，基本都认为不能存储NULL值，但也有一些持反对意见的人，认为其他数据库索引能存储NULL值，为什么ORACLE不可以？那到底实际情况什么样子？无论哪种观点，总应该找权威的观点来做理论支持，不能人云亦云(做技术的人就是这么较劲，来不得半点马虎)。在翻阅了大量资料后，搜集到相关内容如下：1.B*Tree indexes, except in the ...
oracle:让is null使用索引
weixin_43230594的博客
11-17 535
有一个有用的索引技巧可以帮助调整这个语句。创建一个索引并将常量值添加到索引末尾，以便存储 NULL 值。Oracle 不会在索引中存储 NULL 值，因此即使 T1.ID2 列上确实存在索引，它也无法使用。这一次，sql选择走index,并且cost更低。
如何在Oracle中查询某个字段为null时使用索引
list2016的博客
05-09 2171
记录目的 在Oracle查询sql中即使某个字段加入索引，在查询字段 is null的情况下，也会索引失效。本文目的是如何在判断字段为空的情况下也使用索引。因实操环境为公司封闭开发环境，无法展示执行计划，具体的需要大家实际操作。 查询sql select * from mobile where phone is null; 下策：使用联合索引 在创建索引时使用联合索引，讲查询字段作为第一个字段，第二个随便加一个字段或者默认值，在数据量少的情况下，此方法有效。在我实验中查询的结果在300条时，会使用索引查询
IS NULL 是否能用到索引？
weixin_40649680的博客
02-20 717
不一定哦
oracle null 不走索引,搜索条件设置为Is Null一定不走索引吗？
weixin_39797324的博客
04-06 1592
声明：本文思路来源于laoxiong老师的博文，就此感谢！在Oralce的搜索中，如果where条件句后使用了is null搜索条件，那么即使该列上使用了索引，Oracle也不会进行索引搜索。那么，这条规则一定正确吗？构建实验环境准备数据环境，构建数据表。SQL> create table t as select object_id,object_name,owner from dba_ob...
Oracle复合索引与空值的索引使用问题小结
12-16
昨天在QQ群里讨论一个SQL优化的问题，语句大致如下： select A,min(B) from table group by A;--A，B都没有not null约束，A列无空值，B列有空值。--存在复合索引IX_TEST(A,B) 于是手动测试，环境采用Oracle自带的scott用户下的emp表。 1.首先查看如下语句的执行计划（此时表只有主键索引）： 2.添加IX_TEST(deptno,comm)后查看执行计划： 发现依然是全表扫描。 3.为deptno列添加非空约束后再次查看执行计划： 4.总结： Btree索引是不存储空值的，这个是所有使用Btree索引的数据库的共同
涂抹Oracle--三思笔记之一步一步学Oracle
07-23
资源名称：涂抹Oracle--三思笔记之一步一步学Oracle内容简介：本书作为一本创作之初就定位于技术应用的实践参考书，虽然前前后后串联了Oracle数据库中十余个常用特性或工具，但在章节的设计上完全遵循这一理念。...
浅谈MyBatis-Plus学习之Oracle的主键Sequence设置的方法
09-07
主要介绍了浅谈MyBatis-Plus学习之Oracle的主键Sequence设置的方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
jansu-dev#Oracle-Learning-Notes#[SQL调优]--Oracle10g优化器因正确的统计信息选择高
07-25
案例背景基本信息节点一：节点二：节点一从AWR报告看，逻辑读达到1.3G/s的逻辑读，节点二甚至更高。节点一：节点二：Top 5 Timed Events节点一
Oracle数据库调优之-SQL执行计划粗解
04-07
数据库调优，其中一个重点就是应用程序的SQL调优。而应用程序的调优范围比较广，可以从调整业务的角度做调优，...需要注意的是，不同的数据库的版本的调优方式有所不同，以下所有的测试结果都是基于Oracle11gr2的版本。
多图表实现员工满意度调查数据分析python
04-22
员工满意度是指员工对于工作环境、待遇、职业发展和组织管理等方面的满意程度。它是衡量员工对工作的整体感受和情绪状态的重要指标。
2020届软件工程本科毕业生毕业设计项目.zip
04-22
2020届软件工程本科毕业生毕业设计项目
基于stm32平衡小车
04-22
平衡小车 基于stm32 平衡小车 基于stm32 平衡小车 基于stm32
c语言火车票订票管理源码.rar
最新发布
04-22
c语言火车票订票管理源码.rar
oracle中的address,OracleStudy之案例--Oracle数据块地址（BlockAddress）
06-13
Oracle中的address指的是数据块地址（Block Address），是Oracle数据库中标示数据块位置的一个唯一标识。每个数据块都有一个固定大小，通常为8KB或者16KB，其中保存着一些记录或者索引等数据。 在Oracle数据库中，数据块是管理存储空间的基本单位，每个数据块都有一个唯一的地址（Block Address）。数据块地址由文件号（File Number）和块号（Block Number）组成，其中文件号指的是数据文件的编号，块号指的是数据块在数据文件中的相对位置。 例如，如果一个数据块的地址为(1,10)，则表示它位于文件1中的第10个数据块。Oracle数据库通过数据块地址来管理数据文件中的数据，通过数据块的读写来实现对数据的访问。 OracleStudy中的案例是一个关于数据块地址的示例，通过这个案例可以了解到如何使用Oracle中的数据块地址来查询数据块的相关信息，包括数据块的大小、文件编号、块编号等信息。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值