|
一些容易忽视的因素，往往是我们进行优化的方向。 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) |