oracle execute plan 概念
一、名詞定義
rowid是實際資料列的系統唯一值
recursive call:為了執行某語法所多執行的額外語法，例如trigger
row source:查詢中 返回符合條件的資料子集，可以是某表的全部或部份或join結果
Predicate:where後面那一串條件限制
driving table:查詢中，返回較少row source的 應做為驅動表，hash join中則叫outer table(外層表)
probed table:又稱內層表，或叫被driving table表，即join中另一個返回較多筆數的
concatenated index:連鎖索引，on (c1,c2,c3...) 要注意c1 是主導鍵
如果 where c2=xxx 就不會用到連鎖索引了
selectivity:返回資料愈少的適合當索引
二、oracle index scan行為
1.全表掃Full Table Scans,FTS
這種模式當查詢返回資料過多時 會用FTS比較有效率
然而，真對大表格，這種方法會進行非常可觀的I/O量
進而影響系統效能
I/O存取量和db_block_multiblock_read_count 有關
2.rowid lookup
速度最快的模式，但幾乎沒人這樣用..
SQL> select * from t1 where rowid='AAAb/eABFAAAHMDAAB';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY USER ROWID) OF 'T1'
3.INDEX scan/lookup
先找到index對應中的rowid，再由rowid找出真正資料的值，前者是邏輯，後者會實體io
如下先找索引再找rowid
3-1. index unique scan 索引唯一值掃瞄
alter table t1 add constraints pk_t1 primary key (user_id);
select username from t1 where user_id=22;
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 1 |
| 2 | INDEX UNIQUE SCAN | PK_T1 | 1 | | 1 |
這user_id內容雖沒重複但建一般index 仍會以range收場
如果設為限制，則會以唯一值掃瞄
3-2 index range scan：當使用 < > <= >= between 時或排序時
SQL> select username,user_id from t1 where user_id<22;
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 |
| 2 | INDEX RANGE SCAN | I_T1_ID | 1 | | 1 |
select * from t1 where user_id <30 order by user_id;
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 468 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 26 | 468 | 3 |
| 2 | INDEX RANGE SCAN | I_T1_ID | 26 | | 2 |
3-3 index full scan 索引全掃
select user_id from t1 order by user_id;
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 262 | 1048 | 3 |
| 1 | INDEX FULL SCAN | PK_T1 | 262 | 1048 | 3 |
如果下這種就會被成全掃
select * from t1 order by user_id;
------------------------------------------
| 0 | SELECT STATEMENT | | 262 | 4716 | 3 |
| 1 | SORT ORDER BY | | 262 | 4716 | 3 |
| 2 | TABLE ACCESS FULL| T1 | 262 | 4716 | 2 |
3-4 index fast full scan ，和3-3的差別在於沒有排序
select user_id from t1;
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262 | 1048 | 2 |
| 1 | INDEX FAST FULL SCAN| PK_T1 | 262 | 1048 | 2 |
三、表格的合併查詢
表格的join有 對等聯合a.c1=b.c1、非等值聯a.c1<b.c1 =, 外連a.c1=b.c1(+)
而聯合的模式則有
Sort Merge Join SMJ 排序合併聯
Nested Loops NL 巢狀回圈
Hash join 哈希聯
1.SMJ
先處理第一個表格，產生row source-a,再處理第二個表格產生 row source-b
兩個合併
如果row source-a,b在合拼前已排序，速度會變快
#建兩個表格t1,t2都不建索引在8i和10g (默認hash)
select a.username,b.username from t1 a, t2 b where a.user_id=b.user_id order by a.user_id,b.user_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'T2'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'T1'
10G 不管建不建索引 一律hash join
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262 | 10742 | 6 |
| 1 | SORT ORDER BY | | 262 | 10742 | 6 |
| 2 | HASH JOIN | | 262 | 10742 | 5 |
| 3 | TABLE ACCESS FULL| T1 | 262 | 2882 | 2 |
| 4 | TABLE ACCESS FULL| T2 | 262 | 7860 | 2 |
------------------------------------------------------------
2.Nested Loops NL
這種聯合排序最好把外層表用最小row source的當驅動表
理論上會最少io次數，但實際則不定
外層 內層
row source1.row1 --- probe>row source 2
row source1.row2 --- probe>row source 2
因為外層的每一個對映內層所有行
故外圈(驅動表)要小，內圈(被驅表)要有索引(最好是唯一索) 會有最佳 解
例如t1 ,t2建索引
8i t1,t2不管單建 或各建索引 情況都會比都不建好些
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'T2'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
5 4 INDEX (RANGE SCAN) OF 'I_T1_ID' (NON-UNIQUE)
3.Hash join, HJ
oracle 7.3以後新增，理論上比SMJ,NL更有效率，且只用在CBO上
它的作法是用較小row source那個當hash table和bitmap
第二個表用來被hansed與第一個表格產生的 hash table 搭配
hash_join_enabled=true
hash_area_size
只能用在等值聯結 a.c1 = b.c1
先把index拿掉，回到兩個都fts時 再
analyze table t1 estimate statistics;
analyze table t2 estimate statistics;
select a.created from t1 a, t2 b where a.user_id=b.user_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=161 Bytes=2093)
1 0 HASH JOIN (Cost=3 Card=161 Bytes=2093)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=1 Card=161 Bytes=1610)
3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=161 Bytes=483)
4.Cartesian product
笛卡兒乘級
這個非常可怕，無限乘上去，一般有設定2-3層 即停止 免得拖死整個db
a.n * b.m
有幾行就乘上去 結果嚇死人
留言列表
