数据列not null对索引影响一例-CSDN博客

一些容易忽视的因素，往往是我们进行优化的方向。

Oracle数据列是有not null属性的，标志该列是否允许出现空值。如果插入、修改出现空值的情况，Oracle会拒绝当前的DML操作。

本质上看，数据列的not null是一种检验约束，其效果的时点是在数据列发生变化的时候，如果出现为空的数据，就拒绝操作。此外，not null约束还会影响到优化器产生执行计划。

很多人都认为只有在where条件后面加入索引列，或者索引列的一部分（组合索引），SQL语句才会走索引。其实这是一种误解。

索引是一种数据库对象，在执行计划的生成过程中，给Oracle CBO优化器更多的执行路径选择，用来找出更好更快的执行路径。

我们构建一个实验环境。

SQL> desc t;

Name Type Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER VARCHAR2(30) Y

OBJECT_NAME VARCHAR2(128) Y

SUBOBJECT_NAME VARCHAR2(30) Y

OBJECT_ID NUMBER Y //注意：此时Object_id列是允许为空的，即使数据取值是没有空值；

DATA_OBJECT_ID NUMBER Y

OBJECT_TYPE VARCHAR2(19) Y

CREATED DATE Y

LAST_DDL_TIME DATE Y

TIMESTAMP VARCHAR2(19) Y

STATUS VARCHAR2(7) Y

TEMPORARY VARCHAR2(1) Y

GENERATED VARCHAR2(1) Y

SECONDARY VARCHAR2(1) Y

//构造索引

SQL> create index IDX_T_ID on t(object_id);

//收集统计量

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

PL/SQL procedure successfully completed

首先，我们进行针对object_id的全值搜索。

SQL> select object_id from t;

已选择51355行。

已用时间: 00: 00: 00.46

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 51355 | 250K| 160 (2)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T | 51355 | 250K| 160 (2)| 00:00:02 |

--------------------------------------------------------------------------

统计信息

----------------------------------------------------------

251 recursive calls

0 db block gets

4138 consistent gets

712 physical reads

0 redo size

743413 bytes sent via SQL*Net to client

38038 bytes received via SQL*Net from client

3425 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

51355 rows processed

查询SQL没有where条件语句，只是返回object_id列。很自然没有使用索引，进行的操作是全表扫描。

评说：这似乎很正常，也很容易说服人。返回所有数据，where条件中没有条件。

但是，这实际上是存在一定的优化空间的。笔者认为：对于二维数据表集合，当我们纵向优化（结果集数量）没有着手点的时候，可以考虑横向（列）上做文章。

思路：where条件中虽然没有条件项目，但是select后的列表中却只有一个object_id，而且是存在以object_id为索引列的索引的。

如果我们对索引B*树结构熟悉的话，就知道索引列值实际上都在B*树叶节点上顺序排列，和对应的rowid在一起。SQL语句的条件虽然不具有搜索索引的条件，但是如果只要求object_id的值，我们只要搜索一遍树的叶子节点，就可以获取到所有的取值了。而不需要再去搜索数据表了，变两次搜索（索引+表）为一次搜索（索引）。

同时，Oracle读取是以块Block作为单位。读取一个数据表块获取的object_id信息肯定大大小于直接读取一个索引块获取的object_id信息。

想得很好，但是刚才的执行计划确实是走了FTS（Full Table Scan），没有按照我们理想中去执行。原因就在于null值。

Oracle是不知道你的数据列有没有空值，或者以后有没有空值。如果有空值，构建的索引树中，就不会有空值对应的数据行信息。按照我们刚才的思路，就会把取null值的object_id遗漏，Oracle是无论如何不会选取这样的路径的。

解决的方法：加入not null约束，明确非空数据。

看下面实验。

alter table T modify OBJECT_ID not null;

SQL> desc t;

Name Type Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER VARCHAR2(30) Y

OBJECT_NAME VARCHAR2(128) Y

SUBOBJECT_NAME VARCHAR2(30) Y

OBJECT_ID NUMBER //非空设置

DATA_OBJECT_ID NUMBER Y

OBJECT_TYPE VARCHAR2(19) Y

CREATED DATE Y

LAST_DDL_TIME DATE Y

TIMESTAMP VARCHAR2(19) Y

STATUS VARCHAR2(7) Y

TEMPORARY VARCHAR2(1) Y

GENERATED VARCHAR2(1) Y

SECONDARY VARCHAR2(1) Y

//刷新buffer cache

SQL> alter system flush buffer_cache;

System altered

执行相同查询。

SQL> select object_id from t ;

已选择51355行。

已用时间: 00: 00: 00.46

执行计划

----------------------------------------------------------

Plan hash value: 1588161578

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 51355 | 250K| 28 (4)| 00:00:01

| 1 | INDEX FAST FULL SCAN| IDX_T_ID | 51355 | 250K| 28 (4)| 00:00:01

--------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

96 recursive calls

0 db block gets

3552 consistent gets

124 physical reads

0 redo size

743413 bytes sent via SQL*Net to client

38038 bytes received via SQL*Net from client

3425 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

51355 rows processed

结果，在修改not null属性之后，果然Oracle选择了索引搜索路径，采用了Index Fast Full Scan的操作。这种操作是可以并行的高效搜索方法，而且是针对索引列值直接返回结果。

其他性能指标中，我们也容易看出这样做的优势。

#	FTS全表扫描	索引路径
执行时间	00: 00: 00.46	00: 00: 00.46
CPU成本	160	28
逻辑读consistent reads	4138	3552
物理读physical reads	712	124
递归调用recursive call	251	96