Oracle数据库解决NULL值不走B树索引

B树索引我们可以把它看成是书的目录,在这个目录中主要记录的是索引所对应的表列的值和这个值所对应的ROWID。在通常情况下,我们在表中增加索引的目的是增加表的查询性能,但是有几种情况,即使你在表中加入了索引,Oracle也不会执行索引。下面来说明一下其中一种不走索引的情况--null值不入索引。

以下的说明索引只针对B树索引,对于位图索引,是可以记录NULL值的。

首先需要说明的是,有的人会认为Oracle的表中只要有一列(表中的某个属性或字段)没有非空约束,并且该列中存在NULL值,那么对该列(表中的某个属性或字段)做任何查询都不走索引,这事不对的,请看下面的例子。

下面我们来创建一张text_tab表,并为该表的comm字段增加普通B树索引。

SQL> create table text_tab as select * from emp;
 
Table created
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
14 rows selected
 
SQL> create index idx_comm on text_tab(comm);
 
Index created

再来看一下对text_tab的comm字段的查询,之后查看其执行计划。

SQL> alter system flush shared_pool;    --此操作在生产环境中慎用
 
System altered
 
SQL> select count(1) from text_tab where comm = '300';
 
  COUNT(1)
----------
         1
 
SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select count(1) from text_tab%';
 
SQL_TEXT                                                                         SQL_ID        HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ---------- ------------
 select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik 1nm4rhvwjndut 4179244889            0
 select count(1) from text_tab where comm = '300'                                8ykxwd1c1v6zj 1478335473            0

SQL> select * from table(dbms_xplan.display_cursor('8ykxwd1c1v6zj', 0, 'advanced'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8ykxwd1c1v6zj, child number 0
-------------------------------------
 select count(1) from text_tab where comm = '300'
Plan hash value: 4008428181
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |          |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_COMM |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TEXT_TAB@SEL$1
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
 
...
 
48 rows selected

从上面测试中可以看出,Oracle执行了索引,执行计划为索引范围扫描。这说明,在表中的某一列中如果存在NULL值,并不是对此列的任何操作Oracle都不走索引。

下面举一个Oracle因为NULL值不走索引的例子。

SQL> alter system flush shared_pool;    --此操作在生产环境中慎用
 
System altered
 
SQL> select * from text_tab where comm is null;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
10 rows selected
 
SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where comm is%';
 
SQL_TEXT                                                                         SQL_ID        HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ---------- ------------
 select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik a4dd5ksa9uf77  345848039            0
 select * from text_tab where comm is null                                       01g489ph8yjcq 1620002198            0
 
SQL> select * from table(dbms_xplan.display_cursor('01g489ph8yjcq', 0, 'advanced'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  01g489ph8yjcq, child number 0
-------------------------------------
 select * from text_tab where comm is null
Plan hash value: 2822424504
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEXT_TAB |    10 |   870 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TEXT_TAB@SEL$1
Outline Data
-------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

...
 
49 rows selected

通过上面的执行计划可以看出,CBO对该条查询选择的执行计划为全表扫描。这种情况就是我们所说的,NULL值不入索引的情况。

首先来看为什么Oracle的常规B树索引不记录NULL值。
1、索引是有序的。当一个空值进入索引时,无法确定其在索引中的位置。
2、空值与空值不相等。当检索一个空值时,由于空值与空值并不相等,所以,无法在索引中找到期望的空值索引。

但是在某些情况下,由于业务规定或者为了开发便利,我们无法避免这种NULL值的情况,那么如何在无法避免NULL值的情况下,去避免不必要的全表扫描,让其走索引呢?

PS:这里插一句题外话,某些情况下,全表扫描的效率不一定会低于走索引。甚至有些情况下会高于执行索引。因为在Oracle执行索引的时候,会先去索引中查找是否有对应的记录,如果没有找到可以返回的记录,它会找到需要返回记录的rowid,去表块中读取需要返回的值。但是如果一张表有1万条记录,而我们的查询结果大约9000条,这种情况下,先查索引再回表的效率要远远低于全表扫描这样直接去扫描数据块的效率。全表扫描的最大弊病在于全表扫描在同一查询中,执行效率是不可控的,它的性能会随着表中数据的增加而下降。

下面来说明一下再NULL值无法改变的情况下,如何让CBO选择执行索引的执行计划。大概方法有如下几种:

1、通过NVL这类的函数来解决。

2、通过复合索引和非空约束来解决。

3、通过复合索引增加伪列来解决。这种方式是非常推荐的。

下面来分别说明以下这三种解决方案。


1、使用NVL类似函数解决

SQL> drop index idx_comm;    --首先删除之前建立的索引
 
Index dropped
 
SQL> create index idx_comm on text_tab(nvl(comm, -1));    --重新建立索引,指定如果comm为NULL的情况下,值为-1
 
Index created
 
SQL> select * from text_tab where nvl(comm, -1) = -1;    --将is null改为这种写法
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
10 rows selected
 
SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where nvl%';
 
SQL_TEXT                                                                         SQL_ID        HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ---------- ------------
 select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik gg3m6han59y4d 2824140941            0
 select * from text_tab where nvl(comm, -1) = -1                                 7yywfqzkk2rqh 3844169424            0
 
SQL> select * from table(dbms_xplan.display_cursor('7yywfqzkk2rqh', 0, 'advanced'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7yywfqzkk2rqh, child number 0
-------------------------------------
 select * from text_tab where nvl(comm, -1) = -1
Plan hash value: 386593135
--------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEXT_TAB |     1 |   100 |     2   (0)| 00
|*  2 |   INDEX RANGE SCAN          | IDX_COMM |     1 |       |     1   (0)| 00
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

....
 
52 rows selected

2、通过建立复合索引和非空约束来解决

SQL> drop index idx_comm;
 
Index dropped
 
SQL> create index idx_comm on text_tab(comm, empno);
 
Index created
 
SQL> alter table text_tab modify empno not null;
 
Table altered

SQL> alter system flush shared_pool;
 
System altered
 
SQL> select * from text_tab where comm is null;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
10 rows selected
 
SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where comm%';
 
SQL_TEXT                                                                         SQL_ID        HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ---------- ------------
 select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik dtny2zs959kwm  307547027            0
 select * from text_tab where comm is null                                       01g489ph8yjcq 1620002198            0
 
SQL> select * from table(dbms_xplan.display_cursor('01g489ph8yjcq', 0, 'advanced'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  01g489ph8yjcq, child number 0
-------------------------------------
 select * from text_tab where comm is null
Plan hash value: 386593135
--------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEXT_TAB |    10 |   870 |     2   (0)| 00
|*  2 |   INDEX RANGE SCAN          | IDX_COMM |     1 |       |     1   (0)| 00
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

....
 
53 rows selected

3、通过复合索引增加伪列的方式解决。

这种方式的大体思路与上面第二个的思路相似。但是这种方式是我比较推荐的解决方式。因为如果在复合索引中增加一个其他列,而不是一个常量,会降低DML的操作性能。原因是在Oracle数据库中建立索引是有代价的。增加索引带来的负面影响就是会影响DML的性能,insert的时候,Oracle会先到索引中创建KV键值,在插入数据。delete也一样,会先删除索引键值,再删除数据。update的时候,如果update的是索引键值列,性能也会先下降,如果不是,性能不收影响。

至于create index idx_comm on text_tab(comm, 0);中,创建常量0也是有原因的,因为0在Oracle数据库中只占用1个字节。如果设置其他常量,比方说1,他在Oracle中占用2个字节。

SQL> drop index idx_comm;
 
Index dropped
 
SQL> create index idx_comm on text_tab(comm, 0);
 
Index created
 
SQL> alter system flush shared_pool;
 
System altered
 
SQL> select * from text_tab where comm is null;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
10 rows selected
 
SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where comm%';
 
SQL_TEXT                                                                         SQL_ID        HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ---------- ------------
 select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik dtny2zs959kwm  307547027            0
 select * from text_tab where comm is null                                       01g489ph8yjcq 1620002198            0
 
SQL> select * from table(dbms_xplan.display_cursor('01g489ph8yjcq', 0, 'advanced'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  01g489ph8yjcq, child number 0
-------------------------------------
 select * from text_tab where comm is null
Plan hash value: 386593135
--------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEXT_TAB |    10 |   870 |     2   (0)| 00
|*  2 |   INDEX RANGE SCAN          | IDX_COMM |     1 |       |     1   (0)| 00
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

...
 
52 rows selected

 

  • 3
    点赞
  • 0
    评论
  • 7
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

火良

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值