執行計劃-資料訪問方式（全表掃描與4種索引的方式）
執行計劃
Oracle執行計劃的相關概念：
Rowid：系統給oracle資料的每行附加的一個偽列，包含資料表名稱，資料庫id，儲存資料庫id以及一個流水號等資訊，rowid在行的生命週期內唯一。
Recursive sql：為了執行使用者語句，系統附加執行的額外操作語句，譬如對資料字典的維護等。
Row source（行源）：oracle執行步驟過程中，由上一個操作返回的符合條件的行的集合。
Predicate（謂詞）：where後的限制條件。
Driving table（驅動表）：又稱為連線的外層表，主要用於巢狀與hash連線中。一般來說是將應用限制條件後，返回較少行源的表作為驅動表。在後面的描述中，將driving table稱為連線操作的row source 1。
Probed table（被探查表）：連線的內層表，在我們從driving table得到具體的一行資料後，在probed table中尋找符合條件的行，所以該表應該為較大的row source，並且對應連線條件的列上應該有索引。在後面的描述中，一般將該表稱為連線操作的row source 2.
Concatenated index（組合索引）：一個索引如果由多列構成，那麼就稱為組合索引，組合索引的第一列為引導列，只有謂詞中包含引導列時，索引才可用。
可選擇性：表中某列的不同數值數量/表的總行數如果接近於1，則列的可選擇性為高。
————————————————————————————————————————
Oracle訪問資料的存取方法：
1. Full table scans, FTS(全表掃描)：
透過設定db_block_multiblock_read_count可以設定一次IO能讀取的資料塊個數，從而有效減少全表掃描時的IO總次數，也就是透過預讀機制將將要訪問的資料塊預先讀入記憶體中。只有在全表掃描情況下才能使用多塊讀操作。
2. Table access by rowed（透過rowid存取表，rowid lookup）：
由於rowid中記錄了行儲存的位置，所以這是oracle存取單行資料的最快方法。
3. Index scan（索引掃描index lookup）：
在索引中，除了儲存每個索引的值外，索引還儲存具有此值的行對應的rowid值，索引掃描分兩步1，掃描索引得到rowid；2，透過 rowid讀取具體資料。每步都是單獨的一次IO，所以如果資料經限制條件過濾後的總量大於原表總行數的5%－10％,則使用索引掃描效率下降很多。而如果結果資料能夠全部在索引中找到，則可以避免第二步操作，從而加快檢索速度。
根據索引型別與where限制條件的不同，有4種型別的索引掃描：
3.1 Index unique scan（索引唯一掃描）：
存在unique或者primary key的情況下，返回單個rowid資料內容。
3.2 Index range scan（索引範圍掃描）：
1，在唯一索引上使用了range運算子（>,<,<>,>=,<=,between）；2，在組合索引上，只使用部分列進行查詢；3，對非唯一索引上的列進行的查詢。
3.3 Index full scan（索引全掃描）：
需要查詢的資料從索引中可以全部得到。
3.4 Index fast full scan（索引快速掃描）：
與index full scan類似，但是這種方式下不對結果進行排序。
實驗：
SQL> create table school(sid number(4),sname varchar2(400 char), check_status number(1) default 0 check( check_status in(0,1)),accountant varchar2(20 char)unique,pwd varchar2(20 char),email varchar2(30 char),photo_path varchar2(800 char),
constraint pk_t_school primary key(sid));
Table created.
SQL> create sequence shool_sid_autoinc
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
SQL> create or replace trigger insert_shool_sid_autoinc
before insert on school
for each row
begin
select shool_sid_autoinc.nextval into :new.sid from dual;
end;
/
SQL> create table team(sid number(4),tid number(2),tname varchar2(400 char),number_of_teams number(2),mentor varchar2(400 char),constraint pk_t_team primary key(tid),constraint fk_t_school01 foreign key(sid) references school(sid));
Table created.
SQL> create sequence team_tid_autoinc
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
SQL> create or replace trigger insert_team_tid_autoinc
before insert on team
for each row
begin
select team_tid_autoinc.nextval into :new.tid from dual;
end;
/
-- 重複插入十三條記錄（sid不一樣）
SQL> insert into school values(3,'aaaaaaaaaaaaaaaaaaaaa',0,001,001,'964955634@qq.com',66666);
SQL> insert into school values(4,'aaaaaaaaaaaaaaaaaaaaa',0,001,001,'964955634@qq.com',66666);
.......
SQL> insert into school values(15,'aaaaaaaaaaaaaaaaaaaaa',0,001,001,'964955634@qq.com',66666);
SQL> set autotrace on;
1. Full table scans, FTS(全表掃描)
SQL> select * from school;
Execution Plan
----------------------------------------------------------
Plan hash value: 149184061
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1306 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SCHOOL | 1 | 1306 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
2. Table access by rowed（透過rowid存取表，rowid lookup）
SQL> select rowid from school;
ROWID
------------------
AAATdsAAEAAAADHAAA
AAATdsAAEAAAADHAAB
AAATdsAAEAAAADHAAC
AAATdsAAEAAAADHAAD
AAATdsAAEAAAADHAAE
AAATdsAAEAAAADHAAF
AAATdsAAEAAAADHAAG
AAATdsAAEAAAADHAAH
AAATdsAAEAAAADHAAI
AAATdsAAEAAAADHAAJ
AAATdsAAEAAAADHAAK
AAATdsAAEAAAADHAAL
AAATdsAAEAAAADHAAM
13 rows selected.
SQL> select * from school where rowid='AAATdsAAEAAAADHAAA';
Execution Plan
----------------------------------------------------------
Plan hash value: 2354595538
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1318 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| SCHOOL | 1 | 1318 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
3.1 Index unique scan（索引唯一掃描）
SQL> select * from school where sid=3;
Execution Plan
----------------------------------------------------------
Plan hash value: 3749557451
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1306 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SCHOOL | 1 | 1306 | 0 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T_SCHOOL | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
3.2 Index range scan（索引範圍掃描）
SQL> select sid from school where sid<8;
Execution Plan
----------------------------------------------------------
Plan hash value: 3257910080
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_T_SCHOOL | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
3.3 Index full scan（索引全掃描）
SQL> select sid from school;
Execution Plan
----------------------------------------------------------
Plan hash value: 2759332510
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_T_SCHOOL | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
3.4 Index fast full scan（索引快速掃描）
SQL> create index in_t_school_sid_sname on school(sid,sname);
SQL> select * from school where sname='aaaaaaaaaaaaaaaaaaaaa';
Execution Plan
----------------------------------------------------------
Plan hash value: 2494086730
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1306 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SCHOOL | 1 | 1306 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IN_T_SCHOOL_SID_SNAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
總結:
當進行index full scan的時候，oracle定位到索引的root block，然後到branch block（如果有的話），
再定位到第一個leaf block, 然後根據leaf block的雙向連結串列順序讀取。它所讀取的塊都是有順序的，也是經
過排序的。而index fast full scan則不同，它是從段頭開始，讀取包含點陣圖塊，root block,所有的branch
block,leaf block，讀取的順序完全由物理儲存位置決定，並採取多塊讀，資料是無序的，每次讀取
db_file_multiblock_read_count個塊。這就是為什麼兩者的結果區別如此之大的原因。
OK，轉載請標明出處
————————————————————————————————————————
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2131507/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】 索引的掃描方式Oracle索引
- 全表掃描和全索引掃描索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 全表掃描和全索引掃描繼續（PG-TiDB）索引TiDB
- Sqlserver執行計劃中表的四種連線方式SQLServer
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 資料庫訪問幾種方式對比資料庫
- SQLSERVER中得到執行計劃的方式SQLServer
- oracle是如何進行全表掃描的Oracle
- Spring框架訪問資料庫的兩種方式的小案例Spring框架資料庫
- [20190815]索引快速全掃描的成本.txt索引
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言，不涉及CLUSTERING_FACTOR索引
- 進入正在執行的Docker容器的4種方式Docker
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- Locust 多種執行方式
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- [20210219]全表掃描邏輯讀問題.txt
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- 關係型資料庫全表掃描分片詳解資料庫
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- 建立執行緒的三種方式執行緒
- 執行緒建立的四種方式執行緒
- 三種方式本地執行Knative
- 執行緒池建立的幾種方式執行緒
- 併發程式設計 建立執行緒的三種方式程式設計執行緒
- 多執行緒掃描資料夾耗時方法分析執行緒
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 易語言執行js的兩種方式JS
- Java建立多執行緒的四種方式Java執行緒
- 在本地執行Kubernetes的3種主流方式
- Java執行groovy指令碼的兩種方式Java指令碼
- 資料結構線性表兩種方式分享資料結構
- 資料包表多種序號生成方式
- web方式訪問sshWeb
- ECharts海量資料渲染解決卡頓的4種方式Echarts
- 理解資料庫掃描方法-利用掃描方法對資料儲存進行優化資料庫優化