当前位置:  数据库>oracle

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

    来源: 互联网  发布时间:2017-06-04

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

进入CBO优化器时代之后,成本计算值决定执行计划的选取已经成为主流。一条性能良好的执行计划建立在尽可能“贴切”的统计量基础上。CBO内部又经历了两个时代——IO Cost和CPU Cost,两者的区别就在于系统统计量(System Statistical)的应用。
 
RBO时代,执行计划其实也是有评估的。RBO的执行计划评定级别不会像CBO成本粒度那么细,而是15个路径等级评定。等级编号低的执行计划比等级编号高的执行计划更会被选择到。
 
在这个过程中,我们其实还是忽略了影响执行计划的因素,就是约束(Constraint)。Constraint对于数据库对象很重要,所谓约束,就是建立在数据表、数据列上的规则限制。Constraint的存在目的就是将业务规则融入到数据表设计中。
 
Constraint确定描述了数据表的一些固有特性,比如非空、外键,就从一个程度上给出了数据表特性的描述。经常性的将Constraint作为一种数据完整性约束的实现,但是对于CBO而言,约束也是搜寻“捷径”执行计划的重要信息来源。从经验上看,约束能够给CBO带来的高效执行计划作用,是不可忽视的。
 
本篇介绍几个常见的业务场景,说明在合理规划约束的情况下,CBO能够生成更好地执行计划。

--------------------------------------分割线 --------------------------------------

 

相关阅读:

Oracle完整性约束

Oracle的约束和索引

从Oracle的约束到索引

Oracle常用数据类型和完整性约束

ORA-02291: 违反完整约束条件 …… - 未找到父项关键字

--------------------------------------分割线 --------------------------------------

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 |

-------------------------------------------------------------------------------

更多详情见请继续阅读下一页的精彩内容:


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle的dual表作用
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,