oracle 全表扫描和索引扫描

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

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

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

SQL> set autotrace traceonly
SQL> select * from t00_cust_no_mapping_his;
已选择46116行。
执行计划
----------------------------------------------------------
Plan hash value: 973990892
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                         | 46116 |  5764K|   225   (1)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| T00_CUST_NO_MAPPING_HIS | 46116 |  5764K|   225   (1)| 00:00:03 |
---------------------------------------------------------------------------------------------

2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）

        行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。所以oracle中rowid扫描有两层含义：一种是用户在sql语句输入的rowid的值直接去访问对于的数据行记录；另一种是先去访问相关索引，然后根据访问索引后得到的rowid再回表去访问对应的数据行记录

SQL> select * from temp where b='2';

执行计划
----------------------------------------------------------
Plan hash value: 1733176997
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP  |     2 |    40 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_B |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"=2)
Note
-----
   - dynamic sampling used for this statement (level=2)

对于oracle堆表而言，我们可以通过oracle内置的rowid伪列得到对应行记录所在的rowid值（rowid只是一个伪列），可以通过DBMS_ROWID包中相关方法查看，可以将rowid翻译成实际的物理地址值

案例：

 select employee_id, first_name ,rowid,dbms_rowid.rowid_relative_fno(rowid) ||'_'||dbms_rowid.rowid_block_number(rowid)||'_'||dbms_rowid.rowid_row_number(rowid) location from employees;

通过rowid直接访问；

select employee_id, first_name from employees where rowid='AAAEATAAEAAAADNAAA';

例如：employee_id=100 的行记录对于的rowid是AAAEATAAEAAAADNAAA，对rowid翻译后可以看出该行记录存储的实际物理地址是：位于4号文件的第205个数据块的第0行记录（数据块里数据行记录的记录号是从0开始）

3）索引扫描（Index Scan或index lookup）
        我们先通过index查找到数据对应的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%，使用索引扫描会效率下降很多。

案例：

假设一张表含有10万行数据--------100000行
我们要读取其中20%(2万)行数据----20000行
表中每行数据大小80字节----------80bytes
数据库中的数据块大小8K----------8000bytes
所以有以下结果：
每个数据块包含100行数据---------100行
这张表一共有1000个数据块--------1000块

通过索引读取20000行数据 = 约20000个table access by rowid = 需要处理20000个块来执行这个查询

所以：如果按照索引读取全部的数据的20%相当于将整张表平均读取了20次！！这种情况下直接读取整张表的效率会更高

由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。