Oracle 表访问方式
(1)TABLE ACCESS FULL
全表扫表,使用全表扫描的方式访问表。示例:
SQL> create index idx_t1_object_id on t1(object_id);
索引已创建。
SQL> set autot trace;
SQL> select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 88911 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 999 | 88911 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
(2)TABLE ACCESS BY INDEX ROWID
通过索引获得的ROWID访问表。示例:
SQL> select * from t1 where object_id=1;
执行计划
----------------------------------------------------------
Plan hash value: 716510271
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 89 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
(3)LOAD AS SELECT
以(append)追加的方式向表中插入数据。示例:
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 |
---------------------------------------------------------------------------
(4)TABLE ACCESS BY USER ROWID
以用户指定的ROWID方式访问表。示例:
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 |
-----------------------------------------------------------------------------------
(5)TABLE ACCESS BY ROWID RANGE
通过一段范围的ROWID来访问表。示例:
var A VARCHAR2;
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))
(6)TABLE ACCESS CLUSTER
通过簇来访问表。示例:
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)
(7)TABLE ACCESS BY LOCAL INDEX ROWID
通过由本地分区索引获取到ROWID访问表。示例:
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)
(8)TABLE ACCESS BY GLOBAL INDEX ROWID
通过全局分区索引获取到ROWID访问表。(全局分区索引也可以建立在非分区表上)示例:
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)
(9)TABLE ACCESS SAMPLE
通过采样方式访问表,即扫描表的部分数据块。示例:
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 |
----------------------------------------------------------------------------
(10) TABLE ACCESS BY INDEX ROWID
通过索引获得指定范围的ROWID,以采样的方式访问表。示例:
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)