Oracle执行计划之表访问操作
本篇记录一些表访问操作
www.zzzyk.com
显示执行计划的存储过程请参考
http://www.zzzyk.com/database/201303/192688.html
（1）TABLE ACCESS FULL
全表扫表，使用全表扫描的方式访问表。示例：
www.zzzyk.com
[sql]
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访问表。示例： www.zzzyk.com
[sql]
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]
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]
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]
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]
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):
