进入CBO优化器时代之后，成本计算值决定执行计划的选取已经成为主流。一条性能良好的执行计划建立在尽可能“贴切”的统计量基础上。CBO内部又经历了两个时代——IO Cost和CPU Cost，两者的区别就在于系统统计量（System Statistical）的应用。
RBO时代，执行计划其实也是有评估的。RBO的执行计划评定级别不会像CBO成本粒度那么细，而是15个路径等级评定。等级编号低的执行计划比等级编号高的执行计划更会被选择到。
在这个过程中，我们其实还是忽略了影响执行计划的因素，就是约束（Constraint）。Constraint对于数据库对象很重要，所谓约束，就是建立在数据表、数据列上的规则限制。Constraint的存在目的就是将业务规则融入到数据表设计中。
Constraint确定描述了数据表的一些固有特性，比如非空、外键，就从一个程度上给出了数据表特性的描述。经常性的将Constraint作为一种数据完整性约束的实现，但是对于CBO而言，约束也是搜寻“捷径”执行计划的重要信息来源。从经验上看，约束能够给CBO带来的高效执行计划作用，是不可忽视的。
本篇介绍几个常见的业务场景，说明在合理规划约束的情况下，CBO能够生成更好地执行计划。
1、执行环境介绍
我们同时要使用CBO和RBO进行测试过程，选择Oracle 11g进行测试。
SQL> select * from v$version;
BANNER
---------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 – Production
当前默认使用CBO优化器组件。
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
optimizer_use_sql_plan_baselines boolean TRUE
（篇幅原因，有省略……）
2、“null还是not null”大不一样
我们在实际设计数据库中，经常会忽略字段非空设置。不少朋友和开发团队对于这个细节不以为然，认为这个设置就是会影响到插入过程。一些朋友认为：在应用层面验证一下就可以了。但是实际上，null与not null，大不一样！有很多方面的差异和问题，纯应用层面验证是不能解决问题的。
笔者从性能优化器角度，介绍一下忽视not null效果的问题。我们首先创建实验数据表T。
--数据表T
SQL> 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 |
-------------------------------------------------------------------------------
9 rows selected
两个特点，一个是执行计划中首先进行了Index Full Scan对主键索引进行扫描，第二个是进行了Sort Group By Nosort。Sort Group是早期Hash Group出现之前的Group动作。而Sort Group By Nosort是做Sort Group By的动作，但是根本就不会进行排序、也不需要进行Group By。原因是什么？就是由于操作Index Full Scan获取到的结果集合是扫描叶子节点，本身就是有序的，不需要进行排序。不需要进行Group By的原因是每个叶子节点Group By的结果只有一个，就是1。
这种取巧的执行计划，我们在RBO上是看不到的。
SQL> explain plan for select /*+rule*/empno, count(*) from scott.emp group by empno;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 15469362
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS FULL| EMP |
-----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
13 rows selected
我们使用RBO的时候，Oracle进行Group By主键和其他列操作是一样的。
4、空值null Group
一般情况下，我们是不会遇到主键Group By的情况的。大多数的Group By都是有限Distinct值的。这种时候，在CBO情况下避免不了进行全表扫描。即使Group By列中创建了索引，索引也不会用到。
SQL> drop table t purge;
Table dropped
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
此时执行计划如下：
SQL> explain plan for select owner, count(*) from t group by owner;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 47235625
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 192 | 298 (2)| 00:00:04 |
| 1 | HASH GROUP BY | | 32 | 192 | 298 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T | 75609 | 443K| 294 (1)| 00:00:04 |
---------------------------------------------------------------------------
9 rows selected
底层路径没有去走索引路径，根本原因在于object_id是null，允许为空。Group By操作是允许空值进入的。如果系统业务上恰恰不允许object_id设置为非空，怎么办？
问题的策略在于将空值object_id也进入到索引结构。此处可以使用常数组合索引方法。
SQL> create index idx_t_owner_cmp on t(owner,0);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> explain plan for select owner, count(*) from t group by owner;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 685106933
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 192 | 61 (9)| 0
| 1 | HASH GROUP BY | | 32 | 192 | 61 (9)| 0
| 2 | INDEX FAST FULL SCAN| IDX_T_OWNER_CMP | 75609 | 443K| 57 (2)| 0
--------------------------------------------------------------------------------
9 rows selected
执行成本值从原有的近300，下降到当前的61。
5、外键“想当然”
外键Foreign Key是维持参照完整性的重要手段。对CBO而言，Foreign Key意味着取值规则上的差异，也会影响落实到执行计划中。
首先创建实验环境。
SQL> create table t_master (id number, mas_name varchar2(100));
Table created
SQL> create table t_child (cid number, id number, chi_name varchar2(100));
Table created
SQL> alter table t_master add constraint pk_t_master primary key (id);
Table altered
SQL> alter table t_child add constraint pk_t_child primary key (cid);
Table altered
SQL> alter table t_child add constraint fk_t_child_master foreign key (id) references t_master(id);
Table altered
--收集统计量
SQL> exec dbms_stats.gather_table_stats(user, 'T_MASTER', cascade => true);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user, 'T_CHILD', cascade => true);
PL/SQL procedure successfully completed
我们研究一个SQL语句，讨论查找不存在主表中的子表记录。
SQL> explain plan for select * from t_child where id not in (select id from t_master);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1412281931
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 3 (34)| 00:00:01
|* 1 | HASH JOIN ANTI SNA| | 1 | 91 | 3 (34)| 00:00:01
| 2 | TABLE ACCESS FULL| T_CHILD | 1 | 78 | 2 (0)| 00:00:01
| 3 | INDEX FULL SCAN | PK_T_MASTER | 1 | 13 | 0 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
15 rows selected
Not in的标准过程是使用反连接动作，上面的执行计划也证明了这点。但是，这个执行计划没有考虑外键因素。在外键约束中，外键列上是不可能出现不是空值的非主键值。Not in最大的警惕点在于是否有空。
我们调节外键列的属性约束，取得新的执行计划。
SQL> alter table t_child modify id not null;
Table altered
SQL> explain plan for select * from t_child where id not in (select id from t_master);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2639974602
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_CHILD | 1 | 78 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
14 rows selected
成本值是0！执行计划中的确有对子表的FTS动作，但是上面的那个filter，引用了一个条件：null is not null——永假式。
SQL> select 1 from dual where NULL IS NOT NULL;
1
----------
Oracle在承认子表id非空之后，直接判定这个SQL是不会有任何结果的。索性执行给一个永假式，返回0行记录。
这种取巧路径在RBO时代，也是不存在的。
SQL> explain plan for select /*+rule*/* from t_child where id not in (select id from t_master);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2659151261
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| T_CHILD |
|* 3 | INDEX UNIQUE SCAN| PK_T_MASTER |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T_MASTER" "T_MASTER" WHERE
"ID"=:B1))
3 - access("ID"=:B1)
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- rule based optimizer used (consider using cbo)
21 rows selected
6、结论
执行计划是SQL语句执行效率的命脉。如何生成更快速的执行计划，不仅是Oracle，更是所有优化器追求的方向目标。在CBO时代，Oracle放弃了RBO时候简单15个等级的策略，应用成本Cost度量执行计划策略，统计量+内核公式构成了优化器比较基础。
除此之外，我们说数据库对象的描述，特别是约束起到更加重要的作用。本篇中，我们介绍了几个关键场景下，约束对于执行计划的作用。从直观上看，约束描述可以帮助CBO找到各种“取巧”路径。这实际上就反映了约束对于数据库的描述作用，生成高效执行计划。
对于开发设计人员，这个结论很重要。实际工作中，不少开发人员忽视约束的作用。不愿意进行字段非空讨论，不进行适度外键设计，这些都是需要我们借鉴的方面。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1063998/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-1063998/
扫一扫
3324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
