【基礎篇基本原理】資料訪問方式
從前面一些測試中的執行計劃可以看到一些有關資料的訪問方式，比如Full Table Scan, Index Unique Scan, Index Range Scan, Index Full Scan, Index Fast Full Scan, Index Skip Scan, Bitmap index等。
1,Table Access by user rowid
SQL> select rowid, a.object_id
2 from test.t_test_clusterfactor1 a
3 where rowid = 'AAAP3sAAEAAAATUAAA';
ROWID OBJECT_ID
------------------ ----------
AAAP3sAAEAAAATUAAA 2
Execution Plan
----------------------------------------------------------
Plan hash value: 2983979101
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1 | 15 |
1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T_TEST_CLUSTERFACTOR1 | 1 | 15 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
484 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
注意到統計資訊，只有一個consistent gets，這一個一致性讀也比較好理解，只需要一次讀取即可以獲得資料。這種方式是訪問資料最快的方式。
2,Table Access full
全表掃描一次性讀取多個資料塊，每個塊讀取資料到高水位線處。
下面的語句中的rownum = 1也可以改成 <= 1，在oracle 10g版本下面這兩個是一樣的。
SQL> select rowid, a.object_id
2 from test.t_test_clusterfactor1 a
3 where rownum = 1;
ROWID OBJECT_ID
------------------ ----------
AAAP3sAAEAAAATUAAA 2
Execution Plan
----------------------------------------------------------
Plan hash value: 2839102670
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)
| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 1 | 15 | 2 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
484 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
3,index unique scan
索引唯一掃描，針對唯一索引，這種方式訪問資料比較快，每個索引鍵值對應一個rowid，而從一致性讀的數量也可以看到。
SQL> create table t_test_uni_1 as select * from dba_objects;
Table created.
SQL> create unique index idx_test_uni_1 on t_test_uni_1(object_id);
Index created.
SQL> select object_id from t_test_uni_1 a where a.object_id = 2;
OBJECT_ID
----------
2
Execution Plan
----------------------------------------------------------
Plan hash value: 2691534793
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:
01 |
|* 1 | INDEX UNIQUE SCAN| IDX_TEST_UNI_1 | 1 | 13 | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
412 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
4,index range scan
普通索引常用的訪問方式，先通過給定的數值範圍找到相應索引的branch block，在對應的leaf地址中查詢leaf block，再從找到的這些leaf block中找滿足的rowid。
SQL> select object_id from t_test_uni_1 a where a.object_id < 3;
Execution Plan
----------------------------------------------------------
Plan hash value: 842571182
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:0
1 |
|* 1 | INDEX RANGE SCAN| IDX_TEST_UNI_1 | 1 | 13 | 2 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"<3)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
412 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
5,index fast full scan
SQL> select object_id from t_test_uni_1 a where a.object_id > 2;
55048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 100569850
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 55048 | 215K| 28 (4)| 00:
00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TEST_UNI_1 | 55048 | 215K| 28 (4)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."OBJECT_ID">2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3784 consistent gets
0 physical reads
0 redo size
797531 bytes sent via SQL*Net to client
40759 bytes received via SQL*Net from client
3671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55048 rows processed
SQL>
上面的例子中，優化器認為FFS要比TFC快，在FFS期間，oracle讀取B樹索引上的所有葉塊，且可以一次性讀取多個塊，如果表查詢中所有的列都包括在了索引中，就有可能使用FFS，當然也可以使用hint來選擇FFS。下面看看使用不同方式訪問索引的例子：
SQL> create index idx_test_uni_1_owner_id on t_test_uni_1(owner,data_object_id);
Index created.
SQL>
SQL> select owner,data_object_id from t_test_uni_1 a
2 where data_object_id > 3;
5343 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3246279645
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 5361 | 37527 | 33
(4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TEST_UNI_1_OWNER_ID | 5361 | 37527 | 33
(4)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA_OBJECT_ID">3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
503 consistent gets
0 physical reads
0 redo size
97247 bytes sent via SQL*Net to client
4316 bytes received via SQL*Net from client
358 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5343 rows processed
SQL>
SQL> select owner,data_object_id from t_test_uni_1 a
2 where data_object_id = 3;
Execution Plan
----------------------------------------------------------
Plan hash value: 3105119147
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1 | 7 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_UNI_1 | 1 | 7 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_UNI_1_DATAO | 1 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
476 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select /*+ index_ffs(a)*/owner,data_object_id from t_test_uni_1 a
2 where wner = 'SYS';
23223 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3246279645
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 2394 | 16758 | 33
(4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TEST_UNI_1_OWNER_ID | 2394 | 16758 | 33
(4)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1690 consistent gets
0 physical reads
0 redo size
320570 bytes sent via SQL*Net to client
17428 bytes received via SQL*Net from client
1550 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23223 rows processed
SQL>
SQL> select /*+ index(a)*/owner,data_object_id from t_test_uni_1 a
2 where wner = 'SYS';
23223 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 295890889
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 2394 | 16758 | 8 (0)
| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST_UNI_1_OWNER_ID | 2394 | 16758 | 8 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1598 consistent gets
0 physical reads
0 redo size
320570 bytes sent via SQL*Net to client
17428 bytes received via SQL*Net from client
1550 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23223 rows processed
SQL>
6，index skip scan
當索引的第一列並沒有出現在where中時，可以通過skip scan的方式來使用組合索引。
SQL> select /*+ index(a IDX_TEST_UNI_1_OWNER_ID)*/count(*) from t_test_uni_1 a
2 where data_object_id = 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 475888785
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 2 | 24 (0)
| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 |
| |
|* 2 | INDEX SKIP SCAN| IDX_TEST_UNI_1_OWNER_ID | 1 | 2 | 24 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=2)
filter("DATA_OBJECT_ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
7,bitmap index
當bitmap索引建立的合適時，對於一些查詢也是很高效的。
SQL> create bitmap index idx_bitmap_test_uni1 on t_test_uni_1(object_type);
Index created.
SQL>
SQL> select /*+ */* from t_test_uni_1 a where a.object_type = 'TABLE';
2347 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1626142262
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1376 | 115K| 174 (2)| 00:00:03
|
|* 1 | TABLE ACCESS FULL| T_TEST_UNI_1 | 1376 | 115K| 174 (2)| 00:00:03
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
913 consistent gets
0 physical reads
0 redo size
119335 bytes sent via SQL*Net to client
2116 bytes received via SQL*Net from client
158 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2347 rows processed
SQL>
SQL> select /*+ index(a idx_bitmap_test_uni1)*/* from t_test_uni_1 a where a.object_type = 'TABLE';
2347 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1471543534
--------------------------------------------------------------------------------
---------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
--------------------------------------------------------------------------------
---------------------
| 0 | SELECT STATEMENT | | 1376 | 115K|
174 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | T_TEST_UNI_1 | 1376 | 115K|
174 (0)| 00:00:03 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
| |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_BITMAP_TEST_UNI1 | | |
| |
--------------------------------------------------------------------------------
---------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
293 consistent gets
0 physical reads
0 redo size
119519 bytes sent via SQL*Net to client
2116 bytes received via SQL*Net from client
158 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2347 rows processed
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-668404/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mybatis基於註解的方式訪問資料庫MyBatis資料庫
- Pandas庫基礎分析——資料生成和訪問
- ABP框架之——資料訪問基礎架構框架架構
- 資料訪問層基礎結構設計
- ABP框架之——資料訪問基礎架構（下）框架架構
- Nginx實戰基礎篇四通過https方式訪問web伺服器NginxHTTPWeb伺服器
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- 【資料庫】Redis基礎篇資料庫Redis
- 【基礎篇基本原理】sql執行過程SQL
- Android基礎與應用 資料儲存與訪問Android
- 資料庫訪問幾種方式對比資料庫
- Redis基礎篇（八）資料分片Redis
- 前端資料之美 -- 基礎篇前端
- 【執行計劃】資料訪問方式,連線方式及方法
- 大資料解決方案-（基礎篇）大資料
- 《MySQL 基礎篇》七：資料型別MySql資料型別
- Python 快速教程（基礎篇02）：基礎資料型別Python資料型別
- Spring Boot 2.x基礎教程：使用JdbcTemplate訪問MySQL資料庫Spring BootJDBCMySql資料庫
- MongoDB資料庫操作詳解：基礎篇MongoDB資料庫
- Python訪問Oracle的兩種資料獲取方式PythonOracle
- MySQL基礎之----資料型別篇(常用資料型別)MySql資料型別
- redis基礎篇——資料一致性Redis
- 前端基礎 - 資料型別篇（高頻面試！！！）前端資料型別面試
- 《MySQL 基礎篇》六：表和資料的管理MySql
- 大資料基礎教程：建立RDD的二種方式大資料
- JS 基礎篇(一)：建立物件的四種方式JS物件
- 資料訪問層
- Laravel-配置 Nginx 資料夾 / 子目錄訪問-重定向方式LaravelNginx
- GUI應用程式該以何種方式訪問資料庫?GUI資料庫
- Oracle 表訪問方式Oracle
- Oracle 索引訪問方式Oracle索引
- oracle表訪問方式Oracle
- web方式訪問sshWeb
- C#基礎訪問修飾符概述C#
- 服務端指南 | 基於資料的訪問控制服務端
- 資料結構與演算法——基礎篇（一）資料結構演算法
- Redis基礎篇（六）資料同步：主從複製Redis
- 執行計劃-資料訪問方式（全表掃描與4種索引的方式）索引