数据列not null对索引影响一例
时间:2014-12-08 14:25 来源:linux.it.net.cn 作者:IT
一些容易忽视的因素，往往是我们进行优化的方向。
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 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
通过对比，我们发现明显的性能优化趋势。
反思：这里，优化器很明显是参考了not null信息，进入了决策因素。Oracle有选择索引进行搜索的备选，但是不能确定该列是否可能为null。当确定了not null之后，可以保证所有索引列值都会进入索引生成，在叶子节点保存，于是就选择了索引搜索路径。
(责任编辑：IT)
| 一些容易忽视的因素，往往是我们进行优化的方向。 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 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 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 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 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的操作。这种操作是可以并行的高效搜索方法，而且是针对索引列值直接返回结果。 其他性能指标中，我们也容易看出这样做的优势。
通过对比，我们发现明显的性能优化趋势。 反思：这里，优化器很明显是参考了not null信息，进入了决策因素。Oracle有选择索引进行搜索的备选，但是不能确定该列是否可能为null。当确定了not null之后，可以保证所有索引列值都会进入索引生成，在叶子节点保存，于是就选择了索引搜索路径。 (责任编辑：IT) |