读书概要:基于oracle的sql优化: 第一章 oracle里的优化器

一、oracle里面的优化器
1、优化器(Optimizer)是oracle数据库中的内置的一个核心子系统,一个核心模块或者核心功能组件,优化器的目的按照一定的判断原则得到目标Sql在当前情形下的最高效的执行路径(Access Path)[得到sql的执行计划]

2、优化器分类:RBO(Rule-Based Optimizer),CBO(Cost-Based Optimizer)

  RBO:A:基于硬编码在oracle中的一系列固定规则,决定sql执行计划;      B:适用对象:OLTP类型sql语句,oracle给各种类型的执行路径定一个等级,总过十五个等级(1-15),越小执行等级越高, 其中ROWID查询效率最高,全表扫描效率最低;(oracle10g之后不被oracle支持,但是我们任然可以用RULE Hint来用RBO)      C:RBO的缺陷:执行出现问题,无法进行调整,目标sql的写法,对象的先后顺序,对执行计划的效率都会有影响并且oracle好的功能和特性都不能得到RBO的支持;  CBO:A:基于判断原则为成本,从sql中选择成本最小的一个执行计划来执行,成本值根据sql语句涉及到的表索引,列等相关对象来统计(就是统计sql对IO,CPU的消耗)      B:几个特殊概念:        Cardinality(集的势):就是指定集合所包含的记录数(就是结果集的行数),值越大,成本就越大;        Selectivity(可选择率):可选择率=施加指定谓语条件后返回的结果集的记录数/未施加指定谓语条件                             后返回的结果集的记录数;越大,成本越大;        Transitivity(可传递性):对原有sql进行等价改写,提供更多的执行路径给CBO选择;       C:CBO的缺陷:默认目标sql语句where条件的各个列之间是独立的,没有关联关系;                  假设所有的目标sql都是单独执行的,并且互相不干扰;                  对直方图统计信息有诸多限制;                  在解析多表关联的目标sql时,可能会漏选正确的执行计划;                  多表关联目标sql时,可能会漏选正确的执行计划;

3、优化器模式:优化器模式由参数OPTIMIZER_MODE的值来决定,包括:RULE,CHOOSE,FIRST_ROWS_n(1,10,100,1000),FIRST_ROWS,ALL_ROWS.

RULE:ORACLE将使用RBO来解析目标SQL,此时SQL中涉及的各个对象的统计信息对于RBO来说将没有任何作用CHOOSE:在9i中OPTIMIZER_MODE默认值,他表示oracel在解析目标sql的时候到底使用RBO还是使用CBO取决与该SQL中所涉及的表对象是否有统计信息,有就用CBO,没有就用RBO;FIRST_ROWS_n(1,10,100,1000):可以FIRST_ROWS_1,FIRST_ROWS_10,FIRST_ROWS_100,FIRST_ROWS_1000的任意一个值,当OPTIMIZER_MODEDE的值为任意其中的一个,oracle会用CBO来解析SQL,此时的CBO在计算该sql的各条执行路径的成本值是,侧重点在于以最快的响应速度返回头n(n=1,10,100,1000)条记录;FIRST_ROWS:在9i已经过时,在解析sql中会联合使用CBO和RBO,和FIRST_ROWS_n(1,10,100,1000)一样,特殊情况会用自己内置规则执行计划而不考虑成本.ALL_ROWS:是10g之后以及后续oracle数据库版本中OPTIMIZER_MODE的默认值,成本值侧重点在于最佳的吞吐量(最小的系统IO和CPU消耗)

4、结果集(Row Source):指定执行结果的集合

5、访问数据的方式:

A:访问表的方法全表扫描(执行时间会随着数据量的递增而递增);ROWID扫描(物理存储地址;B:访问索引的方法B树索引索引唯一性扫描(INDEX UNIQUE SCAN):适用于where条件是等值查询的sql语句;索引范围查询(INDEX RANGE SCAN):适用于所有的B树索引,当扫描的对象是唯一性索引是,目标SQL的where条件一定是范围查询,不是唯一索引,就没有限制(既可以是等值,也可以是范围查询);索引全扫描(INDEX FULL SCAN):适用于查询列全部是目标索引的索引键值列的情形;索引快速扫描(INDEX FAST FULL SCAN):适用于CBO,可以使用多块读,也可以并行执行,不一定结果集为有序; 索引跳跃式扫描(INDEX SKIP SCAN):适用于那些目标索引前导列的distinct值数量较少,后续非前导列的可选择性又非常好的情形,执行效率一定会随着目标索引前导列的distinct值数量的递增而递减

6、表链接

A:表链接顺序:不管多少表链接,实际执行都是两两链接(驱动表,被驱动表)B:表链接方法:两个表之间的表链接方法有排序合并链接,嵌套循环链接,哈希链接,和笛卡尔链接;排序合并链接:两个表来做表链接时用排序操作(Sort)和合并操作(Merge)来得到结果集;C:嵌套循环链接:链接时依靠两层嵌套循环(外层循环和内存循环)得到结果集;哈希链接:依靠哈希运算来得到结果集;笛卡尔链接:链接是没有任何链接条件的链接方法反连接:半链接:星形链接:用于数据仓库类型的应用,它是一种单个事实表(Fact Table)和多个维度表(Dimension Table)之间的链接D:访问单表的方法:全表扫描,索引,ROWID等;E:表链接的类型:内连接(Inner Join):是指表链接的链接结果只包含那些满足链接条件的记录,目标sql中where条件中没有写标准SQL中的表示外连接的关键词(还有这个符号+),内连接的oracle中的标准写法join on 或者join using外链接(Outer Join):除了包含满足链接条件的记录之外还包含驱动表中所有不满足该链接条件的记录,其中分为Left Outer Join,Right Outer Join,Full Outer Join,(+)出现在那个表后面就表明那个表会议null值来填充那些不满足链接条件并位于该表中的查询列,此时应该以关键词(+)对面的表作为外链接的驱动表,这里的关键是决定那个表是驱动表