Oracle 表访问方式

本篇记录一些表访问方式

索引访问方式请参考：http://blog.csdn.net/laoshangxyc/article/details/8652251

显示执行计划的存储过程请参考

http://blog.csdn.net/laoshangxyc/article/details/8630842

（1）TABLE ACCESS FULL

全表扫表，使用全表扫描的方式访问表。示例：

SQL> exec sql_explain('select * from t1');
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |    10 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

PL/SQL procedure successfully completed.

（2）TABLE ACCESS BY INDEX ROWID

通过索引获得的ROWID访问表。示例：

SQL> exec sql_explain('select * from t1 where id=1');
Plan hash value: 2347959165
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)

PL/SQL procedure successfully completed.

（3）LOAD AS SELECT

以（append）追加的方式向表中插入数据。示例：

SQL> exec sql_explain('insert /*+ append */ into t1 select * from t1');
Plan hash value: 1069440229
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      |    10 |    80 |     3   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT    | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |    10 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

PL/SQL procedure successfully completed.

（4）TABLE ACCESS BY USER ROWID
以用户指定的ROWID方式访问表。示例：

SQL> exec sql_explain('select * from t1 where rowid=''AAAR33AAEAAAACEAAA''');
Plan hash value: 487051824
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| T1   |     1 |     8 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

（5）TABLE ACCESS BY ROWID RANGE

通过一段范围的ROWID来访问表。示例：

SQL> exec sql_explain('select * from t1 a where  a.rowid>:A');
Plan hash value: 1216763554
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     2 |    16 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY ROWID RANGE| T1   |     2 |    16 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A".ROWID>CHARTOROWID(:A))

PL/SQL procedure successfully completed.

（6）TABLE ACCESS CLUSTER

通过簇来访问表。示例：

SQL> exec sql_explain('select * from dept_10 where department_id=:A');
Plan hash value: 2151594128
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     1 |   133 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS CLUSTER| DEPT_10       |     1 |   133 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN  | IDX_PERSONNEL |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=TO_NUMBER(:A))
Note
-----
- dynamic sampling used for this statement (level=2)

PL/SQL 过程已成功完成。

（7）TABLE ACCESS BY LOCAL INDEX ROWID

通过由本地分区索引获取到ROWID访问表。示例：

SQL>  exec sql_explain('select * from info where id=1');
Plan hash value: 3053108795
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |     1 |    11 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |              |     1 |    11 |     2   (0)| 00:00:01 |     1 |     4 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| INFO         |     1 |    11 |     2   (0)| 00:00:01 |     1 |     4 |
|*  3 |    INDEX RANGE SCAN                | ID_LOCAL_IDX |     1 |       |     1   (0)| 00:00:01 |     1 |     4 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=1)

PL/SQL procedure successfully completed.

（8）TABLE ACCESS BY GLOBAL INDEX ROWID

通过全局分区索引获取到ROWID访问表。（全局分区索引也可以建立在非分区表上）示例：

SQL> exec sql_explain('select * from info1 where id=1');
Plan hash value: 170147769
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     2 |    22 |     3   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| INFO1         |     2 |    22 |     3   (0)| 00:00:01 |     4 |     4 |
|*  2 |   INDEX RANGE SCAN                 | ID_GLOBAL_IDX |     5 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)

PL/SQL procedure successfully completed.

（9）TABLE ACCESS SAMPLE

通过采样方式访问表，即扫描表的部分数据块。示例：

SQL> exec sql_explain('select * from t1 sample(2)');
Plan hash value: 411371848
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    37 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS SAMPLE| T1   |     1 |    37 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

（10） TABLE ACCESS BY INDEX ROWID

通过索引获得指定范围的ROWID，以采样的方式访问表。示例：

SQL> exec sql_explain('select * from t1 sample(2) where empno>:A');
Plan hash value: 1306276067
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">TO_NUMBER(:A))
filter(ORA_HASH(ROWID,0,2007369931,'SYS_SAMPLE',0)<85899346)

PL/SQL procedure successfully completed.