读书笔记-《基于Oracle的SQL优化》-第一章-3

优化器：

1、优化器模式：

用于分析目标SQL在Oracle中使用的优化器的类型，以及在使用CBO时计算成本值的重点。这里的“重点”是指当使用CBO计算目标SQL各执行路径的成本值时，计算成本值的方法会因优化器模式而异。

在Oracle中，优化器的模式是由参数OPTIMIZER_MODE的值决定的。

RULE：这意味着Oracle将使用RBO来分析目标SQL，此时SQL中所涉及的对象的统计信息对RBO没有影响。

CHOOSE：Oracle 9i的默认值意味着RBO或CBO取决于SQL涉及的表对象是否有统计信息。

FIRST_ROWS_n(n=1,10,100,1000)：此时，CBO计算SQL各执行路径的成本值的重点是以最快的响应速度返回头n(n=记录1、10、100、1000)条。

FIRST_ROWS：Oracle 9i中已经过时的参数，在某些特殊情况下，RBO中的一些内置规则将被用来选择执行计划，而不考虑成本。例如，当发现相关索引可以避免排序时，选择索引对应的执行路径，不再考虑成本，这显然是不合理的。此时，索引全扫描的概率比以前更高，因为索引全扫描可以避免排序。

ALL_ROWS：Oracle OPTIMIZER_MODE在10g及以后版本中的默认值表示使用CBO分析目标SQL。此时，CBO计算SQL各执行路径的成本值的重点是最佳吞吐量(即系统I/O和CPU资源的最小消耗量)。

2、结果集：

指包含指定执行结果的集合。对于RBO来说，虽然结果集的概念也适用于RBO，但在相应的执行计划中没有对相关执行步骤对应的结果集的描述。对于CBO，在相应的执行计划中列出(Rows)反映了CBO相应执行步骤对应的输出结果集的记录数(Cardinality)的估算值。

3、访问数据的方法：

3.1 访问表的方法

全表扫描：

当Oracle访问目标表中的数据时，它将从表中占据的第一个区域(Extent)的第一个块(Block)开始扫描，一直扫描到表的高水位线(HWM)，必须读取此范围内的所有数据块。

ROWID扫描：

当Oracle访问目标表中的数据时，它直接通过数据所在的ROWID进行定位和访问。ROWID表示Oracle中的数据行记录的物理存储地址，即ROWID实际上对应于Oracle中数据块中的行记录。

ROWID扫描有两种含义：一种是根据用户在SQL语句中输入的ROWID值直接访问相应的数据行记录；另一种是先访问相关索引，然后根据访问索引后获得的ROWID回表访问相应的数据行记录。

对于Oracle堆表，相应的ROWID值通过Oracle内置的ROWID伪列获得(注:ROWID只是一个伪列，在实际表块中不存在)，然后根据DBMS_ROWID包中的相关方法(dbms_rowid.rowid_relative_fno、dbms_rowid.rowid_block_number和dbms_rowid.rowid_row_number)将上述ROWID伪列的值翻译成相应数据行的实际物理存储地址。

访问索引的方法：

(1)、索引唯一性扫描：INDEX UNIQUE SCAN，仅适用于where条件中等值查询的目标SQL。由于扫描对象是唯一性索引，索引唯一性扫描的结果最多只会返回一个记录。

(2)、索引范围扫描：INDEX RANGE SCAN，当扫描对象是唯一索引时，目标SQL的where条件必须是范围查询(谓词条件是BETWEN、<、>等)；当扫描对象是非独特索引时，对目标SQL的where条件没有限制(可以是等值查询，也可以是范围查询)。

在相同条件下，当目标索引的索引行数量大于1时，索引范围扫描所消耗的逻辑读数至少会超过相应的索引唯一扫描。

(3)、索引全扫描：指扫描目标索引所有叶片块的所有索引线。但这并不意味着所有需要扫描索引的分支。默认情况下，在进行索引全扫描时，Oracle只需访问索引最左边叶片块的必要分支块，就可以使用索引叶片块之间的双向指针链表，从左到右扫描索引所有叶片块的所有索引线。

按索引键值排序，达到排序效果。避免真正的排序。

在默认情况下，索引全扫描的有序性决定了全扫描不能并行执行，通常使用单块阅读。

索引全扫描的前提是目标索引至少有一个索引键列属性 NULL。

快速全扫描索引：INDEX FAST FULL SCAN，所有叶片块的索引行都需要扫描目标索引。

与索引全扫描的区别：

（1）、快速全扫描索引只适用于CBO。

（2）、索引快速全扫描可用于多读或并行执行。

（3）、索引快速全扫描结果不一定有序。因为Oracle是根据索引行在磁盘上的物理存储顺序扫描的，而不是根据索引行的逻辑顺序扫描的。因此，扫描结果不一定有序（对于单个索引叶片块中的索引行，其物理存储顺序与逻辑存储顺序一致，但对于物理存储位置相邻的索引叶片块，块与块之间的物理存储顺序在逻辑上不一定有序。

索引跳跃式扫描：INDEX SKIP SCAN，它使得在where条件下没有指定目标索引的前导列查询条件的目标SQL仍然可以使用该索引，但同时指定了该索引的非前导列查询条件，就像在扫描该索引时跳过其前导列一样。这是因为Oracle帮助你遍历了索引前导列的所有distinct值。

Oracle中的索引跳跃扫描只适用于目标索引前导列的distinct值数量较少、后续非前导列可选性较好的情况，因为随着目标索引前导列distinct值数量的增加，索引跳跃扫描的执行效率肯定会降低。

表连接

当优化器分析含表连接的目标SQL时，除了根据目标SQLSQL文本的写作方法来确定表连接的类型外，还必须决定以下三件事才能获得最终的执行计划。

（1）、表连接顺序

（2）、表连接方法

（3）、访问单表的方法

表格连接类型：

（1）、内连接

只要where条件中没有写标准SQL中定义或Oracle中自定义表示外部连接的关键字，SQL的连接类型就是内部连接。

用JOIN连接标准SQL中内连接的写法 ON或JOIN USING。

目标表1 join 目标表2 on (连接条件)

目标表1 join 目标表2 using (连接列集合)

注：使用JOINN注： 对于USING的标准SQL，如果连接列同时出现在查询列中，则在连接列之前不能带有表名或表名的别名（alias），否则，Oracle会报错（ORA－25154）。

特殊的JOIN USING，我们称之为NATURALA JOIN，使用NATURAL JOIN表连接的连接列是表连接的两个表的所有同名列。

目标表1 natural join 目标表2

相当于：目标表1 join 目标表2 using (目标表1和目标表2的所有同名列集合)

（2）、外连接

左连接：目标表1 left outer join 目标表2 on (连接条件)

或

目标表1 left outer join 目标表2 u si n g (连接列集合)

left outer join左侧的目标表1作为表连接的驱动表，表示位于left的表为outer table，驱动表。此时，除了目标表1和目标表2中所有符合连接条件的记录外，连接结果还包括驱动表（目标表1）中所有不符合连接条件的记录。同时，驱动表（目标表2）中所有不符合连接条件的记录对应的查询列将填写NULL值。

右连接：目标表1 right outer join 目标表2 on (连接条件)

或

目标表1 right outer join 目标表2 using (连接列集合)