Oracle表扫描的几种方式

  最近在看数据库调优方面的资料，数据表的几种扫描方式之前也看过，但一直没有做一个详细的记录来明确这些，这次借这个机会好好学习和整理一下。



1.Full Table Scans(全表扫描)

这种方式是访问表最普通的方式，会扫描数据表位于高水位线之下的所有数据块。

发生在没有过滤条件、缺乏主键和索引的情况下对表的访问。


全表扫描是多块读，也就是一次读取多个数据块，读取的块的个数取决于
DB_FILE_MULTIBLOCK_READ_COUNT  这个参数设置，同时还受操作系统的文件块大小的限制，一次读取的数据块的大小之和不能超过当前操作系统的文件块大小。


2.Rowid Scans

通过ROWID的方式扫描全表一般是走索引扫描之后得到ROWID，然后通过ROWID来访问数据，直接通过ROWID来访问数据是最快的方式，因为ROWID直接记录了数据的对象号、文件号、块号和数据行在数据块内的地址槽。

当然直接通过ROWID访问表的方式比较少见，这里仅供参考。

3. Index Scans(索引扫描)

索引扫描是为了快速获取数据所用的最常见的访问表的方式，以至于很多人访问数据一慢，就认为是没有走索引，实际上这个是错误的，并不是说走了索引就一定比其他的方式要快，当然大多数情况下索引扫描确实会带来较快的查询速度，以下就来看一看索引扫描的几种方式。

a) Index Unique Scans (索引唯一扫描)

Index Unique Scans最多会返回一个ROWID，然后通过此ROWID获取实际的数据

Index Unique Scans主要在对有Unique Index和Primary Key 约束的列上进行过滤时。

 b) Index Range Scans(索引范围扫描)

Index Range Scans发生在No_Unique Index的列上访问数据，以及在Unique Index上使用除"="之外的其他过滤条件。

c) Index skip scans(索引跳跃扫描)

索引跳跃扫描主要发生在对复合索引的列进行过滤时，没有写上先导列，并且先导列中的重复值较多，而非先导列中的重复数据较少。

这个说明比较复杂，具体看以下的例子(摘自Oracle Concepts）

a table employees (sex, employee_id, address) with a composite index on (sex, employee_id). Splitting this composite index would result in two logical subindexes, one for M and one for F.

For this example, suppose you have the following index data:

('F',98)
('F',100)
('F',102)
('F',104)
('M',101)
('M',103)
('M',105)

The index is split logically into the following two subindexes:

• The first subindex has the keys with the value F.

• The second subindex has the keys with the value M.

 

上表 employees中，在sex和employee_id上建立了联合索引，由于sex为先导列，则在这个列上又按照值的分布会产生若干逻辑子索引，此例中一个是'F',一个是'M'。

Figure 13-2 Index Skip Scan Illustration
Description of "Figure 13-2 Index Skip Scan Illustration"

The column sex is skipped in the following query:

SELECT * 
   FROM employees 
WHERE employee_id = 101; 

A complete scan of the index is not performed, but the subindex with the value F is searched first, followed by a search of the subindex with the value M.

如上图，当通过此联合索引查找一个具体的行记录时，会通过先导列的值的分支来对其他非先导列进行过滤，如果在 ‘F’ 中找到了101的值，则不会再扫描剩下的值为'F' 的其它行记录，而直接sex值为'M' 中继续查找employee_id 值为101的行记录。

当过滤条件中只有employee_id而没有id时，Oracle优化器会根据具体的IO来选择是做全扫描还是索引跳跃扫描。

create table t1(id int,name varchar2(30),address varchar2(30));

create or replace procedure p1
as
begin
 for i in 1 .. 100001
   loop
     insert into t1 values(1,to_char(i||'aaa'),'chris');
     insert into t1 values(2,to_char(i||'bbb'),'chrisb');
   end loop;

 end;

create index ind_t1 on t1(id,name);


ANALYZE TABLE T1 COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

select * from t1 where name='3aaa';

SELECT STATEMENT, GOAL = ALL_ROWS            5    1    21
 TABLE ACCESS BY INDEX ROWID    BOLAN    T1    5    1    21
  INDEX SKIP SCAN    BOLAN    IND_T1    4    1   

由此可见，索引跳跃式扫描就产生了。

 

d) Index Full Scans(索引全扫描)

如果查询的目标列上建有索引，则会直接走索引全扫描来获取数据，并且获取到的数据是有序排列的。

 

e) Fast Full Index Scans(索引快速全扫描)

与索引全扫描类似，当所访问的列包含在索引范围内时。但与索引全扫描不同的是，索引快速扫描是多块读，所以速度比索引全扫描要快，但取出的结构并不是有序的，而索引全扫描取出的结构则是按照索引的默认循序排列好的。

 

注意：如果要走索引全扫描和索引快速扫描，被扫描的列中至少要有一列为非空，这个是硬性规定。

另外，在10G上的测试结果来看，索引全扫描和索引范围扫描得出来的结构居然一样，是否是我的例子错误还是ORACLE在机制上做了变更？

 

f) Index Joins(索引合并)

关于这个比较疑惑，来看一下ORACLE的解释吧：

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.

 

从字面意思来看，当几张表进行联合查询的时候，如果所选取的列上均有索引，则会进行索引合并来提高检索效率，具体我测试了一下，结果如下：

select a.id from t1 a,t2 b where a.id=b.id

SELECT STATEMENT, GOAL = ALL_ROWS            509    200005    1200030    491
 HASH JOIN            509    200005    1200030    491
  INDEX FAST FULL SCAN    BOLAN    IND_T1    145    200002    400004    141
  INDEX FAST FULL SCAN    BOLAN    IND_T2    84    179016    716064    81

在索引扫描之后有一个HASH JOIN。