Oracle 表訪問方式
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)
（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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2137956/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle表訪問方式Oracle
- Oracle訪問表的方式Oracle
- Oracle 索引訪問方式Oracle索引
- oracle 索引訪問的幾種方式Oracle索引
- Oracle 單表訪問路徑Oracle
- ORACLE 透明閘道器訪問 MYSQL 表OracleMySql
- Oracle訪問表的執行計劃Oracle
- Oracle動態執行表不可訪問Oracle
- Python訪問Oracle的兩種資料獲取方式PythonOracle
- 執行計劃-資料訪問方式（全表掃描與4種索引的方式）索引
- grpc提供http訪問方式RPCHTTP
- 利用10046事件研究三種資料表訪問方式（上）事件
- 利用10046事件研究三種資料表訪問方式（下）事件
- ORACLE 表連線方式Oracle
- oracle表連線方式Oracle
- ruby 訪問新浪微博API post方式和get方式API
- 常見SQL訪問索引的方式SQL索引
- 查詢出資料庫中預設會以全表掃描方式訪問的表資料庫
- Oracle 訪問路徑Oracle
- Oracle外網訪問Oracle
- mono 訪問 oracle、mysqlMonoOracleMySql
- oracle 限定ip訪問Oracle
- 階梯訪問表優化優化
- 限制訪問表的FOR UPDATE操作
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- JVM中物件訪問定位兩種方式JVM物件
- Oracle EBS DMZ區訪問問題Oracle
- 用Python訪問OraclePythonOracle
- 【Oracle】v$表和v_$同義詞的訪問許可權Oracle訪問許可權
- 【執行計劃】資料訪問方式,連線方式及方法
- mariadb配置允許遠端訪問方式
- smarty中三種變數的訪問方式變數
- 兩種訪問介面的方式(get和post)
- Oracle指令碼(Oracle Scripts) – 檢視索引訪問次數及索引訪問型別Oracle指令碼索引型別
- 從Oracle訪問SQL Server（GATEWAYS）OracleSQLServerGateway
- ORACLE 訪問MYSQL 配置筆記OracleMySql筆記
- .net之oraclecommand訪問oracleOracle
- Oracle資料庫訪問控制Oracle資料庫