ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据列not null对索引影响一例

数据列not null对索引影响一例

原创 Linux操作系统 作者:Tomthe 时间:2011-08-16 15:28:45 0 删除 编辑

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

 

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信息。

 

 

想得很好,但是刚才的执行计划确实是走了FTSFull 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之后,可以保证所有索引列值都会进入索引生成,在叶子节点保存,于是就选择了索引搜索路径。

ü       对一些关键数据表(海量),无论是多小的一个操作,都存在性能问题的隐患。要在开发设计阶段就主动进行识别,之后密切关注。及时修正设计和开发方式。不要等待最后性能测试乃至投产之后才开始着手,此时大局已定,入手点较少;

ü       索引路径不是只在where条件后出现才会出现在执行计划中。CBO优化器智能程度很高,在统计量正常的情况下,一般是可以获取到正确的路径和方法的;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20577218/viewspace-704990/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-06-16

  • 博文量
    15
  • 访问量
    24796