執行計劃-資料訪問方式（全表掃描與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，轉載請標明出處
————————————————————————————————————————