Oracle SQL 表访问路径分析

几乎对所有的SQL语句来说，Oracle的SQL引擎都会有不止一种的方式来检索所需要的记录（数据）。当Oracle解析一个SQL语句时，它必须决定采用哪种方式来检索数据是最快的。决定这个获取数据的最佳途径的过程称为查询优化。查询优化应用于所有的查询和执行数据访问的其它语句（例如DML类型的语句）。

        Oracle的SQL引擎的优化器在对SQL语句进行优化时，需要确定采用什么样优化的方式来访问表中的数据。所以我们需要理解表的访问路径方法。下面先介绍一些基本概念，以便于理解查询优化时，表中数据的访问方法。

        Oracle 的SQL优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于SQL语句存取的任何表中的任何行，可能存在许多存取路径(存取方法)，通过它们可以定位和查询出需要的数据。优化器选择其中自认为是最优化的路径。

        在物理层，oracle读取数据，一次读取的最小单位为数据库块(由多个连续的操作系统块组成)，一次读取的最大值由操作系统一次I/O的最大值与db_file_multiblock_read_count参数共同决定，所以即使只需要一行数据，也是将该行所在的数据库块读入内存。逻辑上，oracle用如下存取方法访问数据：

　　1、全表扫描(FullTable Scans, FTS)

　　这是最常见的表访问路径方式。如果数据表上没有创建索引，或者索引失效等都会采用全表扫描方式访问表中的数据。当然有一些情况，不得不采用全表扫描。

        为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_file_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。

　　由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。

　　使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。

　　使用全表扫描的例子：

        2、通过ROWID的表存取(TableAccess by ROWID或rowid lookup)

　　行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。

　　为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。

　　这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。

　　使用ROWID存取的方法：

        首先可以通过查询rowid列，获取表中行数据对应的ROWID的值：

        然后再查询行数据时，使用rowid作为查询条件：

        可以当看到执行计划中，数据的访问方式为 Table access by user rowid：

        3、索引扫描(Index scan或index lookup)

　　我们先通过索引查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。

　　在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：(1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率下降很多。

　　如下列所示，这是一个索引扫描访问表中数据的例子，采用了index full scan descending 方式。

        由于在employee_id列上存在这唯一性索引，在执行查询时，SQL优化器经过分析，决定采用EMP_EMP_ID_PK主键索引来访问表中的数据。在SQL语句中，存在Order by子句，所以在执行计划的ID=2的操作中，采用了Index full scan descending的访问方法。

        下面介绍一下索引扫描的几种方式：

        根据索引的类型与where限制条件的不同，有4种类型的索引扫描：

•         索引唯一扫描(index unique scan)
• 　　索引范围扫描(index range scan)
• 　　索引全扫描(index full scan)
• 　　索引快速扫描(index fast full scan)

　　1、 索引唯一扫描(index unique scan)

　　通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如我们在Employees表的Employee_id列上设置了主键，则Oracle会自动在主键列上创建一个唯一性索引。则在SQL查询语句中使用 where empoyee_id=198 作为查询条件时，语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。如果存在UNIQUE 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。

　　使用唯一性约束的例子：

2、索引范围扫描(index range scan)

　　使用一个索引存取多行数据，此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)。另外，如下所示，在last_name列上，创建了索引，在查询语句中使用 where last_name='OConnell'作为查询条件，查询优化器选择使用index range scan方式访问表中的数据。

　　使用索引范围扫描的例子：

        在非唯一索引上，查询条件谓词可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

　　使用index rang scan的3种情况：

　   (a) 在唯一索引列上使用了range操作符(>、 <、<>、 >=、 <=、 between)

　   (b) 在组合索引上，只使用部分列进行查询，导致查询出多行

　   (c) 对非唯一索引列上进行的任何查询。

 

　　3、索引全扫描(index full scan)

　　与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在CBO模式下才有效。

        CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

　　全索引扫描的例子：

        4、索引快速扫描(index fast full scan)

　　扫描索引中的所有的数据块，与index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

　　索引快速扫描的例子：

我们可以看到，SQL优化器使用了INDEX FAST FULL SCAN的访问方式。

PS：我们在Employees上创建了组合索引：

create index IDX_emps_empnojobid on employees(employee_id , job_id)

        上面仅仅是介绍了SQL查询优化器对表访问的方式，通过执行计划我们可以查看到不同的访问方式。对于性能优化而言，在后续的文章中，会继续分析什么样的表访问方式是最优的。