《基于 Oracle 的 SQL 优化》笔记 第一章 Oracle 里的优化器

CBO 相关基础

cardinality :指定结果集的行数,实际上表示对目标 SQL 的某个具体步骤的执行结果所包含记录数的估算。如果是针对整个目标 SQL,那么此时的 cardinality 就表示对该 SQL 最终执行结果所包含记录数的估算。cardinality 值越大,对应的成本就越高。

selectivity 选择度:是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。计算公式为 selectivity = 指定谓词条件后返回结果集的记录数 / 未施加任何谓词条件的原始结果集的记录数。这个取值处于 0 - 1 之间,值越小越好,说明选择性高(能过滤更多不符合条件的数据)。

对于每一种具体情况,selectivity 会有不同的计算公式。

最简单的情况是对目标列做等值查询时 selectivity 的计算公式,在目标列上没有直方图且没有 NULL 值的情况下,用目标列做等值查询时的 selectivity 的计算公式为 selectivity = 1 / NUM_DISTINCT,NUM_DISTINCT 表示目标列的 distinct 值的数量。

transitivity 可传递性:是 CBO 在查询转换中所做的第一件事,是指 CBO 可能会对原目标 SQL 做简单的等价改写,即在原目标 SQL 中加上根据该 SQL 现有的谓词条件推算出来的新的谓词条件,这么做的目的是提供更多的执行路径给 CBO 做选择,进而得到更高效执行计划的可能性。

Oracle 中可传递性分下列三种情形:

  • 简单的谓词传递:比如 t1.c1 = t2.c1 and t1.c1 = 10 ,额外添加 t2.c1 = 10
  • 连接谓词传递:比如 t1.c1 = t2.c1 and t2.c1 = t3.c1,额外添加 t1.c1 = t3.c1
  • 外连接谓词传递:比如 t1.c1 = t2.c1(+) and t1.c1 = 10,额外添加 t2.c1 (+)= 10

继续阅读

《基于 Oracle 的 SQL 优化》笔记 第二章 Oracle 里的执行计划

如何查看执行计划的顺序

简单的原则:根据往右的缩进,最右的先执行,即从右往左;缩进相同的,从上往下执行。

Oracle 里的常见的执行计划

与表访问相关的执行计划

全表扫描对应的执行计划关键词是“TABLE ACCESS FULL”;
rowid 扫描对应的关键词是“TABLE ACCESS BY USER ROWID”(目标 SQL 直接指定了 rowid)或 “TABLE ACCESS BY INDEX ROWID”(先检索索引,然后用 rowid 访问表)。

与 B-tree 相关的执行计划

连接类型          执行计划关键词
排序合并连接        SORT JOINMERGE JOIN
嵌套循环          NESTED LOOPS
哈希连接          HASH JOIN
反连接           ANTI(可能是 HASH JOIN ANTI, MERGE JOIN ANTI, NESTED LOOPS ANTI
半连接           SEMI(可能是 HASH JOIN SEMI, MERGE JOIN SEMI, NESTED JOIN SEMI

半连接对连接结果进行必要的去重。

其他执行计划

继续阅读

Oracle B-Tree 索引的结构、特点及索引扫描方式

《Oracle DBA 手记 — 数据库诊断案例与性能优化实践》摘录(P218)

Oracle 数据库中索引的存储结构使用的是 B Tree 的一种变体,称为 B*Tree (B Star Tree),在数据库中存储数据以块为单位。
Oracle B-Tree索引结构

一些术语:

  • 节点 M 的深度:从树根节点到节点 M 的最短路径长度。图中根节点 Root 的深度是 0,节点 L1-1 的深度是 1,节点 L0-1 的深度是 2。
  • 节点 M 的层数:节点 M 的层数与其深度是相同的。
  • 树的高度:树的深度值最大的那个节点,其深度 +1 即为树的高度。图中树的高度为 3。

继续阅读