Oracle约束Constraint对于CBO优化器的作用

进入CBO优化器时代之后，成本计算值决定执行计划的选取已经成为主流。一条性能良好的执行计划建立在尽可能“贴切”的统计量基础上。CBO内部又经历了两个时代——IO Cost和CPU Cost，两者的区别就在于系统统计量（System Statistical）的应用。

RBO时代，执行计划其实也是有评估的。RBO的执行计划评定级别不会像CBO成本粒度那么细，而是15个路径等级评定。等级编号低的执行计划比等级编号高的执行计划更会被选择到。

在这个过程中，我们其实还是忽略了影响执行计划的因素，就是约束（Constraint）。Constraint对于数据库对象很重要，所谓约束，就是建立在数据表、数据列上的规则限制。Constraint的存在目的就是将业务规则融入到数据表设计中。

Constraint确定描述了数据表的一些固有特性，比如非空、外键，就从一个程度上给出了数据表特性的描述。经常性的将Constraint作为一种数据完整性约束的实现，但是对于CBO而言，约束也是搜寻“捷径”执行计划的重要信息来源。从经验上看，约束能够给CBO带来的高效执行计划作用，是不可忽视的。

本篇介绍几个常见的业务场景，说明在合理规划约束的情况下，CBO能够生成更好地执行计划。--------------------------------------分割线 -------------------------------------- 相关阅读：Oracle完整性约束 http://www.linuxidc.com/Linux/2013-04/82171.htmOracle的约束和索引 http://www.linuxidc.com/Linux/2012-12/76228.htm从Oracle的约束到索引 http://www.linuxidc.com/Linux/2010-08/27544.htmOracle常用数据类型和完整性约束 http://www.linuxidc.com/Linux/2013-08/89449.htmORA-02291: 违反完整约束条件 …… - 未找到父项关键字 http://www.linuxidc.com/Linux/2013-08/89159.htm--------------------------------------分割线 --------------------------------------1、执行环境介绍我们同时要使用CBO和RBO进行测试过程，选择Oracle 11g进行测试。 SQL> select * from v$version; BANNER---------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 – Production 当前默认使用CBO优化器组件。 SQL> show parameter optimizer NAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_mode string ALL_ROWSoptimizer_use_sql_plan_baselines boolean TRUE（篇幅原因，有省略……） 2、“null还是not null”大不一样我们在实际设计数据库中，经常会忽略字段非空设置。不少朋友和开发团队对于这个细节不以为然，认为这个设置就是会影响到插入过程。一些朋友认为：在应用层面验证一下就可以了。但是实际上，null与not null，大不一样！有很多方面的差异和问题，纯应用层面验证是不能解决问题的。

笔者从性能优化器角度，介绍一下忽视not null效果的问题。我们首先创建实验数据表T。 --数据表TSQL> create table t as select * from dba_objects;Table created --其他用途索引SQL> create index idx_t_id on t（object_id）;Index created --统计量收集SQL> exec dbms_stats.gather_table_stats（user,"T",cascade => true）;PL/SQL procedure successfully completed  还是我们经常设置的场景，就是没有where条件的count动作。  SQL> explain plan for select count（*） from t; Explained SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT-----------------------------------------------------Plan hash value: 2966233522-------------------------------------------------| Id | Operation | Name | Rows | Cost （%CPU）| Time |-------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 294 （1）| 00:00:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 75609 | 294 （1）| 00:00:04 |---------------------------------------------- 9 rows selected 很正常的执行计划，因为需要检索所有的数据行记录，检索数据表所有的记录是比较直观的想法。这个FTS执行计划成本值294。我们修改一下索引列object_id的属性，将其从原先的null设置为not null。

  SQL> alter table t modify object_id not null;Table altered SQL> explain plan for select count（*） from t;Explained SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------

Plan hash value: 3570898368--------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost （%CPU）| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 48 （3）| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_T_ID | 75609 | 48 （3）| 00:00:01 |-------------------------------------------------------------------------- 9 rows selected  SQL语句中没有where条件，选取的count（*）也没有直接object_id关系。但是执行计划中出现了索引对象，最重要的是执行计划从原来的294下降到48。

观察这个执行计划，路径中出现了Index Fast Full Scan动作，而且没有回表动作。Oracle选择这样的路径思路是这样的：object_id是索引列，所有object_id的取值均在叶子节点上。关键难点在于空置null，Oracle中null值不会进入单键值索引对象叶子节点。这也就是为什么Oracle在object_id不设置为not null时不走索引路径的原因。

Oracle在取巧！在CBO时代，对索引路径的选择是一个非常出巧的地方。RBO时代，十五种等级规则，实际就意味着十五种路径方式。我们看RBO时代，有没有这样的策略。

  SQL> explain plan for select /*+rule*/count（*） from t; Explained SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------

Plan hash value: 2966233522-----------------------------------| Id | Operation | Name |-----------------------------------| 0 | SELECT STATEMENT | || 1 | SORT AGGREGATE | || 2 | TABLE ACCESS FULL| T |-----------------------------------Note----- - rule based optimizer used （consider using cbo） 13 rows selected 从上面情况看，RBO时代没有这样的“取巧”过程。约束not null和null带给我们的不仅仅是数据约束的保证，从这个例子上，可以看到not null还可以带来一些高效的执行计划，实现性能上的提升。 3、主键列的Group By 单纯使用Group By是没有意义的，一般都是伴随着如count、sum等聚合函数方法。另外一个关于Group By的特性是：空值null也会被进行Group By。

主键Primary Key的特性是唯一和非空，如果对其进行Group By，每个对象的操作数量就是1。这个过程其实也是不需要进行真正操作的。下面我们进行测试。  SQL> explain plan for select empno, count（*） from scott.emp group by empno;Explained SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT----------------------------------------Plan hash value: 1749432681-----------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 56 | 1 （0）| 00:00:01 || 1 | SORT GROUP BY NOSORT| | 14 | 56 | 1 （0）| 00:00:01 || 2 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 （0）| 00:00:01 |-------------------------------------------------------------------------------更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/101272p2.htm

Oracle 10.2.0.4上ORA-01882故障解决一例Oracle 11g中的IO Calibrate（IO校准）相关资讯 Oracle约束 Oracle约束Constraint

Oracle约束的分类（03/17/2015 19:28:41）
Oracle数据库中由于没有做任何约束（04/01/2014 17:27:56）
Oracle数据库的字段约束创建和维护（10/02/2013 19:31:45）

Oracle外键约束（Foreign Key）的（12/06/2014 14:41:09）
Oracle数据完整性约束：主键、外键（10/27/2013 16:56:40）
Oracle常用数据类型和完整性约束（08/29/2013 06:04:45）

本文评论查看全部评论（0）