Oracle SQL优化②——访问路径

前言

访问路径指的就是通过哪种扫描方式获取数据，比如全表扫描、索引扫描或者直接通过ROWID获取数据。想要完成SQL优化，就必须深入理解各种访问路径。本文章详细介绍常见的访问路径。

一.常见访问路径

1.TABLE ACCESS FULL

表示全表扫描，一般情况下是多块读，HINT: FULL(表名/别名)。等待事件为 db file scattered read。如果是并行全表扫描，等待事件为 direct path read。在Oracle11g中有个新特征，在对一个大表进行全表扫描的时候，会将表直接读入PGA,绕过buffercache，这个时候全表扫描的等待事件也是 direct path read。一般情况下，我们都会禁用该新特征。等待事件 direct path read在开启了异步I/O(disk_asynch_io)的情况下统计是不准确的。

alter system set "_serial_direct_read"=false;

全表扫描究竟是怎么扫描数据的呢？回忆一下Oracle的逻辑存储结构，Oracle最小的存储单位是块(block)，物理上连续的块组成了区(extent)，区又组成了段(segment)。对于非分区表，如果表中没有clob/blob字段，那么一个表就是一个段。全表扫描，其实就是扫描表中所有格式化过的区。因为区里面的数据块在物理上是连续的，所以全表扫描可以多块读。全表扫描不能跨区扫描，因为区与区之间的块物理上不一定是连续的。对于分区表，如果表中没有clob/blob字段，一个分区就是一个段，分区表扫描方式与非分区表扫描方式是一样的。
对一个非分区表进行并行扫描，其实就是同时扫描表中多个不同区，因为区与区之间的块物理上不连续，所以我们不需要担心扫描到相同数据块。
对一个分区表进行并行扫描，有两种方式。如果需要扫描多个分区，那么是以分区为粒度进行并行扫描的，这时如果分区数据不均衡，会严重影响并行扫描速度；如果只需要扫描单个分区，这时是以区为粒度进行并行扫描的。
如果表中有clob字段，clob会单独存放在一个段中，当全表扫描需要访问clob字段时，这时性能会严重下降，因此尽量避免在Oracle中使用clob。我们可以考虑将clob字段拆分为多个varchar2(4000）字段，或者将clob存放在nosql数据库中，例如 mongodb。

一般的操作系统，一次I/O最多只支持读取或者写入1MB数据。数据块为8KB的时候，一次I/O最多能读取128个块。数据块为16KB的时候，一次I/O最多能读取64个块，数据块