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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- web方式訪問sshWeb
- Oracle 訪問路徑Oracle
- Oracle外網訪問Oracle
- 訪問外部裝置方式
- grpc提供http訪問方式RPCHTTP
- Oracle資料庫限制訪問IPOracle資料庫
- Holer實現Oracle外網訪問Oracle
- 【PRODUCE】Oracle 通過儲存過程限制使用者訪問表資料Oracle儲存過程
- 階梯訪問表優化優化
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- mariadb配置允許遠端訪問方式
- Oracle/MySQL透過odbc訪問PostgreSQL for LightDBOracleMySql
- 如何限制ip訪問Oracle資料庫Oracle資料庫
- 【PRODUCE】Oracle 通過儲存過程限制使用者訪問表資料（二）Oracle儲存過程
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- 資料庫訪問幾種方式對比資料庫
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- Oracle 通過透明閘道器訪問mysqlOracleMySql
- NAS儲存外網遠端訪問的方式
- kubernetes使用traefik的https方式訪問web應用HTTPWeb
- Struts2教程之三Action訪問方式
- LightDB/postgresql內建特性之訪問oracle之oracle_fdw介紹SQLOracle
- Oracle RAC Cache Fusion 系列十三：PCM資源訪問Oracle
- Oracle資料訪問元件ODAC的安裝方法Oracle元件
- 透過Kerberos認證訪問Oracle11gROSOracle
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- 【SQL】SQL表連線方法方式介紹（Oracle/Postgresql）SQLOracle
- Oracle 11.2.0.4 透過透明閘道器訪問mysql 8.0.16OracleMySql
- 【TUNE_ORACLE】列出可以建立組合索引的SQL(回表訪問少數字段)的SQL參考Oracle索引SQL
- postgres_fdw 無法通過域名 訪問外部表問題
- 透過自定義域名 + SSL 的方式訪問 Amazon MQ for RabbitMQMQ
- 在SSRS 2008實現匿名訪問報表PD
- oralce 跨使用者訪問表 同義詞
- 如何使用 Rancher Desktop 訪問 Traefik Proxy 儀表板
- 安裝oracle11g碰到“無法訪問臨時位置”的問題Oracle
- 儲存過程訪問其他使用者的表的問題儲存過程
- Oracle表空間的管理方式（LMT、DMT）--本地和字典管理Oracle
- cmu15545-資料訪問方式：B+樹（B+Tree）