Oracle索引梳理系列（一）- Oracle访问数据的方法

版权声明：本文发布于http://www.cnblogs.com/yumiko/，版权由Yumiko_sunny所有，欢迎转载。转载时，请在文章明显位置注明原文链接。若在未经作者同意的情况下，将本文内容用于商业用途，将保留追究其法律责任的权利。如果有问题，请以邮箱方式联系作者（[e-mail]）。

---

Oracle访问数据的方法

Oracle访问数据主要通过三种办法实现：

1. 通过全表扫描的方式访问数据
2. 通过ROWID访问数据
3. 通过索引的方式访问数据

---

1.1 通过全表扫描访问表(TABLE ACCESS FULL)

1. oracle顺序读取表中所有的行，并逐条匹配WHERE限定条件。
2. 采用多块读的方式进行全表扫描，可以有效提高系统的吞吐量，降低I/O次数。
3. 即使创建索引，oracle也会根据CBO的计算结果，决定是否使用索引。

注意事项：

1. 只有全表扫描时才可以使用多块读。该方式下，单个数据块仅访问一次。
2. 对于数据量较大的表，不建议使用全表扫描进行访问。
3. 当访问表中的数据量超过数据总量的5%—10%时，通常oracle会采用全表扫描的方式进行访问。
4. 并行查询可能会导致优化器选择全表扫描的方式。

示例：

Yumiko@sunny >set autotrace traceonly
Yumiko@sunny >select * from scott.emp;
已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1539  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

其中，红色的“TABLE ACCESS FULL”表示采用的全表扫描。

---

1.2 通过ROWID访问表（TABLE ACCESS BY ROWID）

1. ROWID是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据。
2. ROWID指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法。

注意事项：

1. ROWID作为一个伪列，其数值并不存储在数据库中，当查询时才进行计算。
2. ROWID除了在同一集簇中可能不唯一外，每条记录的ROWID唯一。

示例：

--查询记录的rowid
Yumiko@sunny >select rowid,ename from scott.emp where ename='SMITH';

ROWID              ENAME
------------------ ----------
AAAVREAAEAAAACXAAA SMITH

--利用rowid查询数据
Yumiko@sunny >set autotrace on
Yumiko@sunny >select empno,ename from scott.emp where rowid='AAAVREAAEAAAACXAAA';

     EMPNO ENAME
---------- ----------
      7369 SMITH

执行计划
----------------------------------------------------------
Plan hash value: 1116584662

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    22 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    22 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

其中，红色的“TABLE ACCESS BY USER ROWID”表示采用ROWID方式访问数据。

---

1.3 通过INDEX访问表

1. 通过索引查找相应数据行的rowid，再根据rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描”。
2. 一个rowid对应一条数据行（根据rowid查找结果，仅需要对rowid相应数据的数据块进行一次I/O操作），因此该方式属于“单块读”。
3. 对于索引，除了存储索引的数据外，还保存有该数据对应的rowid信息。
4. 索引扫描分为两步：1）扫描索引确定相应的rowid信息。    2）根据rowid从表中获得对应的数据。

注意事项：

1. 对于选择性高的数据行，索引的使用会提升查询的性能。但对于DML操作，尤其是批量数据的操作，可能会导致性能的降低。
2. 全表扫描的效率不一定比索引扫描差，关键看数据在数据块上的具体分布。

示例：

--查看目标用户的索引对象及其名称
Yumiko@sunny >select owner,object_name,object_type from dba_objects where owner='SCOTT';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SCOTT                          PK_DEPT              INDEX
SCOTT                          DEPT                 TABLE
SCOTT                          EMP                  TABLE
SCOTT                          PK_EMP               INDEX
SCOTT                          BONUS                TABLE
SCOTT                          SALGRADE             TABLE

--查看目标索引所在的表及其列信息
Yumiko@sunny >select index_name,table_name,column_name from dba_ind_columns where index_name='PK_EMP';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
PK_EMP                         EMP                            EMPNO

--打开会话跟踪
Yumiko@sunny >set autotrace on

--使用索引列执行查询并查看执行计划
Yumiko@sunny >select empno,ename from scott.emp where empno='';

     EMPNO ENAME
---------- ----------
      7369 SMITH

执行计划
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    10 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

上面红色的“INDEX UNIQUE SCAN”表示采用了索引扫描。同时根据执行计划，可以清晰地看到索引扫描的执行路径，既先进行索引扫描，然后根据得到的rowid信息进行表数据的访问。