一些容易忽视的因素,往往是我们进行优化的方向。
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之后,可以保证所有索引列值都会进入索引生成,在叶子节点保存,于是就选择了索引搜索路径。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-682684/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-682684/