Oracle sql优化（四）访问和联结方法

一全扫描访问方法

1全表扫描：读取跟对象相关的所有数据块。

当一个查询需要返回表中绝大数的数据行时，比较可能选择使用全表扫描。如果返回的数据在表中的比例较低，可能会使用索引扫描，同时优化器还会考虑数据的存储方式（顺序存储与随机存储），如果以随机存储的方式分散到数据块上，则优化器计算出使用索引来读取表中每一个数据块的时间可能比直接使用全表扫描读取所有数据块然后将每个数据块中不需要的数据舍弃所花的时间还要长。

小结：全扫描是否为高效的选择取决于需要访问的数据块个数以及最终的结果集行数。

所访问数据块数目以及舍弃的数量越大，全表扫描的成本越高。

例：查询数据行和数据块统计信息

2全扫描与多块读取

全扫描运算是多块读取，即一次io调用可以读取多个块，块的最大个数可通过参数db_file_multiblock_read_count参数设置。

注：（1）如果读取超出边界范围的数据块。oracle会在一次调用中读取直到边界范围的数据块，然后发起另一次调用读取剩余的块。

（2）在缓冲区的块会多块读取，不在缓冲区的会发起另一个读取。假设多块读取的计数是16个，需要读取编号是1~16的块。如果偶数编号的块已经在缓冲区缓存了，则不在缓冲区的奇数块只能每次调用读取一个。

（3）操作系统对多块读取的大小可能有限制。

3全扫描与高水位线

全扫描当对扫描进行多块读取调用时，数据块中到高水位线为止都将被读取。这就意味着不需要读取的空数据也被读取了。

高水位线：当数据行被插入到表中时，就会为其分配数据块并将数据行放到其中。

当数据行从表中时删除。高水位线保持不变。

读取额外的空数据将严重降低性能。对于频繁加载和清除的表（使用delete而不是truncate，truncate会清除表结构），全扫描的话响应速度会变慢。

二索引扫描访问方法

1默认索引类型是B树索引。

2索引主要保存列值和行编号。

行编号：唯一标记表中行的伪列。包含两个地址：（1）包含该行的块对应的数据文件的地址；（2）数据行在数据块的地址

例：将行编号解码为可读内容：

因此，当使用索引访问数据行时，会根据谓语进行匹配，然后使用行编号来访问指定的文件/数据块/数据行。

索引访问数据块方式是单块读取。根据行编号取出指定的数据块，仅行编号指定的行被访问。

注：通过索引获取的每一行数据，至少需要访问两个数据块，一个索引块和一个数据块。至少是因为根据索引的大小，

oracle可能首先必须访问多个索引块才能获取到第一个符合条件的列值。

3索引结构

索引包含多个分支块和一个叶子块。分支块保存下一层级分支所包括的值范围信息，用来在索引结构中搜索以获取所需的叶子块。叶子分支包含列值和行编号。

索引的查找方式和叶子数据块到根数据块距离一样的特性详见Btree索引详解

4索引扫描类型

分为：索引范围扫描、索引唯一扫描、索引全扫描、索引跳跃扫描、索引快速全扫描

每种类型都是遍历索引结构，通过单块读取一次访问一个索引块，直到读到叶子索引块，获得与索引值匹配的行编号（rowid），然后利用行编号存储整个行数据的表数据块。

5聚簇因子

它是一个统计信息，表明同一个索引条目的数据行存储的数据块的分散程度，聚簇因子值越高表示聚集度越低，聚簇因子值越低表示聚集度越高。

例：上文所创建的t1，t2表由于数据行在数据块中的存储方式不同（顺序或随机），聚簇因子值不同。

注：其中clustering_factor是聚簇因子

6索引唯一扫描（INDEX UNIQUE SCAN）

使用情况：谓语中包含使用unique或primary key索引的列

读取过程：该类型的索引保证某个特定的值只返回一行数据。索引结构会从根到叶子进行遍历直到某个条目，取出行编号，再使用行编号访问包含这一行数据的数据块。除非特殊情况（数据行是链式的或者包含存储在别处的大对象LOB），需要访问的数据块数等于索引高度加一。（注：（1）索引访问一次io调用只读一个块 （2）索引结构的根、分支、叶子对应独立的块 （3）索引高度：从根到叶子层数   索引分支层级数blevel：索引高度-1

例：

注：（1）table access by index rowid表明对于表数据块的访问通过rowid

（2）步骤2前的*号表示下面列出用来访问索引的条件

7索引范围扫描INDEX RANGE SCAN

使用情况：1谓语中包含将会返回一定范围数据的条件时。索引可以是唯一的或不唯一的，所指定的条件可以是诸如<,>,LIKE,BETWEEN甚至是=运算符。范围越大越有可能选用全扫描运算。注：使用组合索引中非引导列的谓语时，将更可能使用索引跳跃扫描。

2查询中含有order by子句时，由于索引是按照顺序存储的，使用索引来读取数据意味着数据行是按照顺序获取的，可以避免再做一次单独的排序。

例：使用索引范围扫描避免排序

读取过程：从根遍历到叶子索引块，从对应的索引条目开始取出一个行编号然后取出相应的表数据块，再次访问之前的叶子索引块并读取下一个索引条目来获取下一个行编号，取出相应表数据块，重复过程直到所有匹配的数据都被读出。假设返回5行数据并且blevel为3，则总的需要访问的数据块次数将是(5*2)+3=13。如果访问的数据量足够大，则很可能需要访问不止一个叶子块。这种情况，下一个叶子块可以通过存储在当前叶子块中指向下一个叶子块的指针读取，这样就没必要再回到分支块来确定下一个块的位置。

8索引全扫描INDEX FULL SCAN

使用情况：（1）查询的列可通过一列的索引获得

（2）谓语中包含一个位于索引中非引导列上的条件

（3）数据可以通过一个排过序的索引来获取并会省去单独的排序步骤。

（4）查询某一列的最小或最大值而这一列又是索引列时。

读取过程：

例：

（1）

（2）

（3）

注：

（4）

注：这种情况的索引全扫描仅仅对根块、一个或多个分支以及第一个和最后一个叶子块的扫描。

例4中第一种查询同时包含min和max集合运算的例子，优化器选择了全表扫描，可以改写成第二种查询优化器选择了全索引扫描从而提高效率。

9索引跳跃扫描INDEX SKIP SCAN

使用情况：当谓语中包含位于索引中非引导列上的条件，并且引导列的值较少。

详见：索引跳跃式扫描

例：

10索引快速全扫描INDEX FULL SCAN

使用情况：查询列表中所有字段都包含在索引中并且索引中至少有一列具有非空约束时替代全表扫描的。在这种情况下数据通过索引来访问而不用访问表数据块。与其他索引不同，索引快速全扫描不能避免排序，因为数据块是通过无序的多表读取来读取的。

读取过程：索引快速全扫描通过多块读取。

例：

注：该例表明如果没有非空约束，优化器会选择全表扫描。

二联结方法

1联结方法分类和联结顺序

联结的方法有：嵌套循环联结、散列联结、排序—合并联结、笛卡尔联结。

联结的顺序：优化器除了确定表的访问方法还要确定表的联结顺序。

（1）第一对表联结后，以后都是下一张表和上一次联结得到的结果行进行联结。

（2）每个联结都有两个分支，所访问的第一张表称为驱动表，第二张表称为内层表和被驱表。优化器通过使用统计信息和where子句中的筛选条件计算每个表分别返回多少行数据来确定哪张表是驱动表。预估大小最小（就块、数据行以及字节而言）的表通常作为驱动表。尤其当优化器确定其中的一张表基于UNIQUE或primary key约束将最多返回一行的时候。

2嵌套循环联结NESTED LOOPS

使用情况：

过程：

3排序—合并联结SORT JOIN（排序） MERGE JOIN（合并）

使用情况：

过程：

4散列联结

使用情况：

过程：

5笛卡尔联结

使用情况：

过程：

6外联结

使用情况：

过程：