表的访问方法

1.全扫描访问方法

当对一个对象进行全扫描时，与该对象相关的所有数据块都必须取出并进行处理，以确定块中所有包含的数据行是否是你查询的所需要的。请记住Oracle必须将整个数据块读取到内存以取得这个块所存储的数据行的数据，因此，当发生全扫描时，实际上优化器（还有你）需要考虑两件事：必须读取多少个数据块以及每个数据块中有多少数据将被舍弃。此时此刻要理解的是，确定全扫描是否是正确选择并不仅仅基于查询将会返回多少行。

1.1 如何选择全扫描操作

它不仅仅是与数据行有关，还与数据块丢弃有关，将所有这些方面的信息集合起来，就会得出全表扫描是否有意义的结论，即使返回的数据行百分比可能很小。另一方面，即使返回的数据行白分比很高也有可能不应该选择全表扫描。

SQL> create table t1 as select trunc((rownum-1)/100) id,rpad(rownum,100)t_pad from dba_source where rownum<=10000;
Table created.
SQL> create index t1_idx1 on t1(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> create table t2 as select mod(rownum,100) id,rpad(rownum,100) t_pad from dba_source where rownum<=10000;
Table created.
SQL> create index t2_idx1 on t2(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t2',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>true);
PL/SQL procedure successfully completed.

select count(*) from t1 where id=1
Plan hash value: 3695297570
-----------------------------------------------------------------------------
| Id  | Operation  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |    |   |   | 1 (100)|   |
|   1 |  SORT AGGREGATE   |    | 1 | 3 ||    |
|*  2 |   INDEX RANGE SCAN| T1_IDX1 | 100 |300 | 1   (0)| 00:00:01 |

-----------------------------------------------------------------------------
SQL> select count(*) from t2 where id=1;

 COUNT(*)
----------
       100

尽管返回结果相同，但是第一走索引，第二全表扫描，因数据分布情况不一样。

1.2 全扫描与舍弃

请记住，全扫描是否为高效的选择取决于需要访问的数据块个数已经最终的结果集行数，另外数据存储方式在决策过程中扮演重要的角色。此外，全表扫描是否高效选择的另一个关键因素是舍弃。舍弃的行是通过一个筛选谓语来进行验证，被证明是不符合筛选条件后从最终结果集中剔除的数据行。

SQL> select table_name,num_rows,blocks from user_tables where table_name='T2';
TABLE_NAME NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T2    10000 152
1.3 全扫描与多块读取

关于全扫描，你还需要知道的是数据块的如何读取的，全运算扫描将会进行多块读取，也就是说一个单独的IO调用讲会请求多个块而不是仅仅一个。所请求的数据块数目是可变的，实际上可以是从一个到db_file_multiblock_read_count参数所指定的数目范围之间的任意个。例如，如果这个参数设置为16，而表中有160个块，可以通过10次调用就可以获取所有的数据块。

oracle不得读取超过一定边界的范围的数据块，在这种情况下，oracle将会在第一次调用中读取到边界范围的数据块，然后发起另一次调用来读取剩余的数据块。

1.4 全扫描与高水位线

oracle将最多读取到位于表中高水位线的数据块，高水位线标出了表中最后一块有数据写入的数据块，为了保持技术上的正确性，实际上应该被称为“低”高水位线。

当数据行插入一张表中的时候，就会为其分配数据块并将数据行放到其中。

delete数据后，高水位线不会下降，当运行全表扫描的时候，仍然会扫描高水位线的所有数据块，truncate可以下降高水位线。

2 .索引扫描访问方法

默认的索引类型是B-树索引，索引建立在表中的一个或多个列或者列的表达式，将列值和行编号（rowid）一起存储。索引中还保存着一些其他信息，我们只需要关注列值和行编号，行编号是一个用来唯一标记表中的伪列，它是物理表中行数据的内部地址，包含两个地址，其一指向数据表中包含该行的块所存放数据文件的地址，另一个可以直接定位到数据行自身的这一行的再数据块中的地址。

行编号指向一个特定的准确位置，因此当使用索引来访问数据行时，就会对谓语所提供的访问标准来进行匹配，然后使用行编号来访问指定的文件/数据块/数据行，通过索引扫描来进行的数据块访问是通过单块读取来完成的，一旦读取了索引条目，就只会取出行编号所指定的那一块数据，在它被取出以后，就仅有行编号所指定的行被访问。

也就是说对于通过索引扫描所获取的每一行，都至少需要访问两个数据块，至少是一个索引块和一个数据块，

2.1 索引扫描类型

索引扫描有好几种不同的类型，但每种类型都有的共同点就是他们必须遍历索引结构以访问匹配所搜索的叶子索引块。首先，索引的根块将 通过一次单块读取来访问，接下来就是读取分支块。最后，读取了包含所需的起始索引条目的第一叶子索引块。如果索引的高度是4，问了得到所需的叶子块，将会对4个单独的块进行读取。此时，就会读取叶子块中第一个匹配的索引值得行编号，并利用这个行编号来进行一次独立的块读取以获取整个行数据所存储的表数据块。因此，在这个例子中，使用索引从表中取出一行，oracle必须读取5个块：4个索引块和1个表数据块。

索引扫描类型：索引范围扫描，索引唯一扫描，索引全扫描，索引跳跃扫描以及索引快速全扫描。索引的快速全扫描实际上更像是进行一次全表扫描，但由于它扫描的是索引结构

一个非常重要的索引统计信息——聚簇因子（clustering factor）。索引的聚簇因子统计信息帮助优化器生成使用索引的成本信息，并且是表中建立了索引的数据排序优良度的一个度量值，索引的聚簇因子向优化器表明了具有同样的索引值的数据行是不是存放同一个或连续的一系列数据块中，或者数据行是否被分散存放表的多个数据块中。

SELECT t.table_name || '.' || i.index_name idx_name,
       i.clustering_factor,
       t.blocks,
       t.num_rows
  FROM user_indexes i, user_tables t
 WHERE i.table_name = t.table_name
   AND t.table_name IN ('T1', 'T2')
  8   ORDER BY t.table_name, i.index_name;

IDX_NAME      CLUSTERING_FACTOR     BLOCKS   NUM_ROWS
------------------------------------------------------------- ----------------- ---------- ----------
T1.T1_IDX1    154         15210000
T2.T2_IDX1  10000         15210000

尽管大多数时候这种聚簇因子计算上的偏差不会引起足够大的差别以至于导致优化器错误地估计索引成本而放弃索引，单的确可能出现这样的情况。如果优化器没有选择你所希望的使用的索引，它还有可能选择另个能满足谓语的类似数据列上的索引。在这样的情况下，你就需要仔细分析所创建的索引，看看是否存在一种方法可以将几个索引合并为一个复合索引。不要错误地认为可以通过重建索引来“修正”聚簇因子。聚簇因子和表中数据不是索引有关，因此，重建索引对它没有任何影响。

2.2 索引唯一扫描

当谓语中包含使用unique或primary key索引的列作为调节的时候会选用索引唯一扫描。这种类型的索引能够保证对于某个特定的值只返回一行数据。在这种情况下，索引结构将会被从根到叶子进行遍历直到某个条目，取出其编号。然后使用这个行编号来访问包含这一行数据的数据块。计划中table access by index rowid步骤表明了对于表数据块的访问。

SQL> set autotrace traceonly explain
SQL> select * from employees where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |   | 1 |69 | 1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     | 1 |69 | 1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN    | EMP_EMP_ID_PK | 1 |   | 0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

2.3 索引范围扫描

当谓语中包含将会返回一定范围数据的条件时会选用索引范围扫描。索引可以时唯一或者不唯一的，因为是由该条件来确定是否返回多个数据行。所指定的条件可以使用<.>,LIKE,BETWEEN甚至是=运算符，为了能够选用索引范围扫描，范围需要相当仔细地进行选择，范围越大，就越有可能会选用全扫描运算来代替它。

SQL> select * from employees where department_id=60;
Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954
-------------------------------------------------------------------------------------------------
| Id  | Operation    | Name| Rows| Bytes | Cost (%CPU)| Time|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    ||     5 |   345 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     5 |   345 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | EMP_DEPARTMENT_IX |     5 ||     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

索引范围扫描将会从根数据块开始到第一个包含符合特定条件的条目所在的叶子数据块来遍历索引结构。再从那一点开始，从索引条目中取出一个行编号然后取出相应的表数据块（通过索引行编号访问数据表），在第一行被取出之后，之前的叶子索引块将再一次被访问并读取下一个索引条目来获取下一个行编号。这种索引叶子块和表数据块之间的反复来回将会不断持续直到所有匹配的索引条目被读出。因此，所需访问数据块的次数将包括索引的分支块数（可以通过索引的blevel统计信息得出）加上符合条件的索引条目数乘以2.必须要乘以2是因为每取出表中的一行需要访首先访问的索引叶子块取出行编号然后通过该行编号访问数据块。

索引范围扫描的精妙之处在于它能够使用一个升序排列的索引来返回降序排列的数据行。

SQL> select * from employees where department_id in(90,100) order by department_id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
---------------------------------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |  | 9 |   621 |2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR      |  |  |  |       |  |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES  | 9 |   621 |2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX |9 | | 1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

2.4 索引全扫描

在以下情况选择索引全扫描：当没有谓语但是所获取的列表可以通过其中一列的索引来获得，当谓语胡总包含一个位于索引非引导列上的条件，或者数据可以通过一个排过序的索引来获取并且会省去单独排序的步骤。

SQL> select email from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
---------------------------------------------------------------------------------
| Id  | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT | |   107 |   856 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_EMAIL_UK |   107 |   856 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

SQL> select * from employees order by employee_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2186312383
---------------------------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |   |107 |  7383 | 3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |107 |  7383 | 3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | EMP_EMP_ID_PK |107 |   | 1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

索引全扫描运算将会扫描索引结构中每一个叶子块，读取每一个条目的行编号，并读取数据行。每个叶子块都有被访问，这个通常比全表扫描效率要高因为每一个索引块要比数据块包含的更多条目，从而总的需要访问的块数也就相应较少。

SQL> select min(department_id) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 613773769
------------------------------------------------------------------------------------------------
| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     1(0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |   |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX |     1 |     3 |     1(0)| 00:00:01 |
------------------------------------------------------------------------------------------------

SELECT (SELECT MIN(department_id) FROM hr.employees) min_id,
       (SELECT MAX(department_id) FROM employees) max_id
  3    FROM dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 2189307159
------------------------------------------------------------------------------------------------
| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |       |     2(0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |   |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX |     1 |     3 |     1(0)| 00:00:01 |
|   3 |  SORT AGGREGATE   |      |     1 |     3 |   |      |
|   4 |   INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX |     1 |     3 |     1(0)| 00:00:01 |
|   5 |  FAST DUAL   |      |     1 |       |     2(0)| 00:00:01 |
------------------------------------------------------------------------------------------------

在以上情况，当使用索引来快速获取最小值时，这个最小值将会是第一个索引叶子块中第一个条目；当获取最大值时，将会是最后一个索引叶子的最后一个条目。这些特别的例子中索引全扫描并不是真正的全扫描——仅仅是对根块，一个或多个分支块以及第一个和最后一个叶子块的扫描。也就是说找到最大/最小值在所需访问的块数目方面耗费的成本是很低的，速度是最快的，

2.5 索引跳跃扫描

当谓语中包含位于索引中非引导列上的条件，并且引导列的值是唯一的时候会选择索引跳跃扫描。

2.6 索引快速全扫描

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

SQL> alter table hr.employees modify(email null);
Table altered.
SQL> set autotrace traceonly explain
SQL> select email from hr.employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   107 |   856 |     3(0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |   856 |     3(0)| 00:00:01 |
-------------------------------------------------------------------------------

以上例子说明了索引快速全扫描运算为了被选择是如何依赖于非空约束的，如果没有这个约束，将会选择全表扫描运算。