-- 執行計劃釋義
注意：
計劃動作執行時遵循最上最右先執行的原則
執行計劃相關視圖
--------------------------------------------------------------------------------
v$sql
v$sqlarea
v$sql_cs_histogram
v$sql_cs_statistics
v$sql_cs_selectivity
v$sql_shared_cursor
v$sql_bind_metadata
捕獲檢視執行計劃方式
-- 方式一：explain plan for
explain plan for select * from t1;
select * from table(dbms_xplan.display());
-- 方式二 set autotrace on
步驟1：set autotrace
set autotrace on (得到執行計劃，輸出運作結果，最後列印執行計劃和統計資訊）
set autotrace traceonly (得到執行計劃，不輸出運作結果）
set autotrace traceonly explain (得到執行計劃，不輸出運作結果和統計資訊部分，僅展現執行計劃部分）
set autotrace traceonly statistics (不輸出運作結果和執行計劃部分，僅展現統計資訊部分）
set autotrace off; --關閉列印
set timing on; --列印執行時間
set autotrace on explain; --隻顯示執行計劃路徑報告
set autotrace on statistics; --隻顯示統計資訊
-- 常見問題：
sql> set autotrace on;
sp2-0618: 無法找到會話辨別符。啟用檢查 plustrace 角色
sp2-0611: 啟用 statistics 報告時出錯
sql> conn /as sysdba
sql> @d:\app\administrator\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql; /*這個是資料庫安裝路徑下的sql檔案*/
sql> conn ahern/oracle
sp2-0611: 啟用 statistics 報告時出錯
sql> conn sys / as sysdba
sql> grant plustrace to public;
sql> set autotrace on; --成功
步驟2：在此處執行你的sql即可，後續自然會有結果輸出
優點：
1. 可以輸出運作時的相關統計資訊（産生多少邏輯讀，多少次遞歸調用，多少次實體讀的情況）
2. 雖然必須要等語句執行完畢後才可以輸出執行計劃，但是可以有traceonly開關來控制傳回結果不打屏輸出。
缺陷：
1. 必須要等到語句真正執行完畢後，才可以出結果；
2. 無法看到表被通路了多少次。
-- 方式三 statistics_level=all;
步驟1：alter session set statistics_level=all;
步驟2：在此處執行你的sql
步驟3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
1. 可以清晰的從starts得出表被通路多少。
2. 可以清晰的從e-rows和a-rows中得到預測的行數和真實的行數，進而可以準确判斷oracle評估是否準确。
3. 雖然沒有專門的輸出運作時的相關統計資訊，但是執行計劃中的 buffers 就是真實的邏輯讀的多少
1. 必須要等到語句真正執行完畢後，才可以出結果。
2. 無法控制記錄輸屏打出，不像 autotrace 有 traceonly 可以控制不将結果打屏輸出。
3. 看不出遞歸調用的次數，看不出實體讀的多少（不過邏輯讀才是重點）
-- 方式四-通過 dbms_xplan.display_cursor 輸入 sql_id 參數直接擷取
步驟1：
select * from table(dbms_xplan.display_cursor('&sq_id')); -- 該方法是從共享池裡得到）
select * from table(dbms_xplan.display_awr('&sq_id')); -- 這是awr性能視圖裡擷取到的）
select * from table(dbms_xplan.display_awr(sql_id,null,null,'all')); -- 通過視圖 dba_hist_sqlbind 找到語句對應的綁定變量
-- 如果有多個執行計劃，可以用類似方法查出
select * from table(dbms_xplan.display_cursor('5320a2qq3m03x',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
1. 知道sql_id立即可得到執行計劃，和 explain plan for 一樣無需執行；
2. 可以得到真實的執行計劃。
1. 沒有輸出運作時的相關統計資訊（産生多少邏輯讀，多少次遞歸調用，多少次實體讀的情況）；
2. 無法判斷是處理了多少行；
3. 無法判斷表被通路了多少次。
-- 方式五 -10046 trace 跟蹤
alter session set statistics_level=typical;
步驟1：alter session set events '10046 trace name context forever,level 12'; -- 開啟跟蹤
步驟2：執行你的語句
步驟3：alter session set events '10046 trace name context off'; -- 關閉跟蹤
步驟4：找到跟蹤後産生的檔案位置 -- 僅僅提供目前會話跟蹤資訊 v$diag_info
select value from v$diag_info where name = 'default trace file'; -- 目前會話跟蹤檔案
步驟5：tkprof trc檔案 目标檔案 sys=no sort=prsela,exeela,fchela -- 格式化指令
1. 可以看出sql語句對應的等待事件
2. 如果sql語句中有函數調用，sql中有sql，将會都被列出，無處遁形。
3. 可以友善的看出處理的行數，産生的實體邏輯讀。
4. 可以友善的看出解析時間和執行時間。
5. 可以跟蹤整個程式包
缺陷:
1. 步驟繁瑣，比較麻煩
2. 無法判斷表被通路了多少次。
3. 執行計劃中的謂詞部分不能清晰的展現出來。
-- 方式六 awrsqrpt.sql (@$oracle_home/rdbms/admin/awrsqrpt.sql)
步驟1：@?/rdbms/admin/awrsqrpt.sql
步驟2：選擇你要的斷點（begin snap 和end snap)
步驟3：輸入你的sql_id
-- 總結：
生成完整的執行資訊，還包括資料在運作期間的各種資訊。實在找不到優化的點，可以打開這個awr報表進行檢視。
-- 6種方式适用場景的總結
1. 如果某sql執行非常長時間才會出結果，甚至慢到傳回不了結果，這時候看執行計劃就隻能用方法1；(explain plan for "sql")
2. 跟蹤某條sql最簡單的方法是方法1，其次就是方法2；(set autotace on ...)
3. 如果想觀察到某條sql有多條執行計劃的情況，隻能用方法4(dbms_xplan.display_cursor)和方法6(awrsqrpt.sql)
4. 如果sql中含有多函數，函數中套有sql等多層遞歸調用，想準确分析，隻能使用方法5；(10046 trace)
5. 注意：要想確定看到真實的執行計劃，不能用方法1和方法2；
6. 要想擷取表被通路的次數，隻能使用方法3；dbms_xplan.display_cursor (statistics_level=all)
檢視sql語句所屬使用者
-- 檢視sql屬于哪個使用者
select a.sql_text, b.username
from v$sql a, v$session b
where a.hash_value = b.sql_hash_value and a.sql_id='96677tuqfbs6d';
-- 擷取指定sql在記憶體中的執行計劃：
select sql_id, child_number, sql_text
from v$sql
where sql_text like 'select count(1) from emp a where a.dept_no =%';
select * from table(dbms_xplan.display_cursor('sql_id', 0));
-- 查詢sql所有執行計劃
select * from table(sys.dbms_xplan.display_cursor('aca4xvmz0rzup',null))
select * from table(sys.dbms_xplan.display_cursor('9x4fggs2mzu0m',0))
-- 檢視資料庫裡面有多個執行計劃的sql語句的sql_id
select sql_id, count(1) as plan_num
from v$sql
group by sql_id
having count(1) >=2
order by 2 desc;
-- 可以通過下面sql語句查詢對應sql的所有執行計劃或部分執行計劃，分析出現多個執行計劃的原因
select * from table(sys.dbms_xplan.display_cursor('9x4fggs2mzu0m',null))
-- 查詢sql的child number為0的執行計劃
-- 查詢sql的child number為1的執行計劃
select * from table(sys.dbms_xplan.display_cursor('9x4fggs2mzu0m',1))
-- 檢視使用者 ahern 最近執行的sql語句
select /*recentsql*/
s.sql_id,
s.child_number,
s.hash_value,
s.address,
s.executions,
s.sql_text
from v$sql s
where s.parsing_user_id =
(select u.user_id from all_users u where u.username = 'ahern')
and s.command_type in (2, 3, 6, 7, 189)
and upper(s.sql_text) not like upper('%recentsql%')
-- 注意：
若 dbms_xplan.display_cursor 要以 allstats last 格式輸出的話，/*+gather_plan_statistics*/ 這個提示資訊放到查詢語句中是必須的。
select /*+gather_plan_statistics*/ /*plan_statistics1*/ name ,salary from test where name = 'hh';
select s.sql_id,s.child_number,s.hash_value,s.address,s.executions,s.sql_text
from v$sql s
where upper(s.sql_text) like upper('%plan_statistics1%' )
and upper(s.sql_text) not like upper( '%v$sql%');
select * from table (dbms_xplan.display_cursor('4wktu80k1xy5k' , 0, 'allstats last cost' ));
-- 擷取表定義
select dbms_metadata.get_ddl('table','employee_list_list_part','ahern') from dual;
select dbms_metadata.get_ddl('table','test_partition','ahern') from dual;
執行計劃中各字段的描述以及執行計劃中各子產品的描述與舉例
1、基本字段(總是可用的)
id 執行計劃中每一個操作(行)的辨別符。如果數字前面帶有星号，意味着将在随後提供這行包含的謂詞資訊
operation 對應執行的操作。也叫行源操作
name 操作的對象名稱
2、查詢優化器評估資訊
rows(e-rows) 預估操作傳回的記錄條數
bytes(e-bytes) 預估操作傳回的記錄位元組數
tempspc 預估操作使用臨時表空間的大小
cost(%cpu) 預估操作所需的開銷。在括号中列出了cpu開銷的百分比。注意這些值是通過執行計劃計算出來的。換句話說，父操作的開銷包含子操作的開銷
time 預估執行操作所需要的時間(hh:mm:ss)
3、分區(僅當通路分區表時下列字段可見)
pstart 通路的第一個分區。如果解析時不知道是哪個分區就設為key,key(i),key(mc),key(or),key(sq)
pstop 通路的最後一個分區。如果解析時不知道是哪個分區就設為key,key(i),key(mc),key(or),key(sq)
4、并行和分布式處理(僅當使用并行或分布式操作時下列字段可見)
inst 在分布式操作中，指操作使用的資料庫連結的名字
tq 在并行操作中，用于從屬線程間通信的表隊列
in-out 并行或分布式操作間的關系
pq distrib 在并行操作中，生産者為發送資料給消費者進行的配置設定
5、運作時統計(當設定參數statistics_level為all或使用gather_plan_statistics提示時，下列字段可見)
starts 指定sql操作執行的次數
a-rows 操作傳回的真實記錄數
a-time 操作執行的真實時間(hh:mm:ss.ff)
6、i/o 統計(當設定參數statistics_level為all或使用gather_plan_statistics提示時，下列字段可見)
buffers 執行期間進行的邏輯讀操作數量 (為每一步實際執行的邏輯讀或一緻性讀)
reads 執行期間進行的實體讀操作數量
writes 執行期間進行的實體寫操作數量
7、記憶體使用統計
omem 最優執行所需記憶體的預估值
1mem 一次通過(one-pass)執行所需記憶體的預估值
0/1/m 最優/一次通過/多次通過(multipass)模式操作執行的次數
used-mem 最後一次執行時操作使用的記憶體量
used-tmp 最後一次執行時操作使用的臨時空間大小。這個字段必須擴大1024倍才能和其他衡量記憶體的字段一緻(比如，32k意味着32mb)
max-tmp 操作使用的最大臨時空間大小。這個字段必須擴大1024倍才能和其他衡量記憶體的字段一緻(比如，32k意味着32mb)
----------------------------------------
omem 目前操作完成所有記憶體工作區(work aera)操作所總共使用私有記憶體(pga)中工作區的大小，
這個資料是由優化器統計資料以及前一次執行的性能資料估算得出的
1mem 當工作區大小無法滿足操作所需的大小時，需要将部分資料寫入臨時磁盤空間中(如果僅需要寫入一次就可以完成操作，
就稱一次通過，one-pass;否則為多次通過，multi_pass).該列資料為語句最後一次執行中，單次寫磁盤所需要的記憶體大小，
這個由優化器統計資料以及前一次執行的性能資料估算得出的
user-mem 語句最後一次執行中，目前操作所使用的記憶體工作區大小，括号裡面為(發生磁盤交換的次數,1次即為one-pass,
-- 執行計劃中各子產品的描述與舉例
1、預估的執行計劃中的各字段與子產品
sql> explain plan for
2 select * from emp e,dept d
3 where e.deptno=d.deptno
4 and e.ename='smith';
explained.
sql> set linesize 180
sql> set pagesize 0
sql> select * from table(dbms_xplan.display(null,null,'advanced')); --使用dbms_xplan.display函數獲得語句的執行計劃
plan hash value: 351108634 --sql語句的哈希植
---------------------------------------------------------------------------------------- /*執行計劃部分*/
| id | operation | name | rows | bytes | cost (%cpu)| time |
----------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 117 | 4 (0)| 00:00:01 |
| 1 | nested loops | | 1 | 117 | 4 (0)| 00:00:01 |
|* 2 | table access full | emp | 1 | 87 | 3 (0)| 00:00:01 |
| 3 | table access by index rowid| dept | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | index unique scan | pk_dept | 1 | | 0 (0)| 00:00:01 |
query block name / object alias (identified by operation id): --這部分顯示的為查詢塊名和對象别名
-------------------------------------------------------------
1 - sel$1 --sel$為select 的縮寫，位于塊1,相應的還有del$,ins$,upd$等
2 - sel$1 / e@sel$1 --e@sel$1，對應到執行計劃中的操作id為2上，即在表e上的查詢，e為别名，下面類同
3 - sel$1 / d@sel$1
4 - sel$1 / d@sel$1
outline data --提綱部分，這部分将執行計劃中的圖形化方式以文本形式來呈現，即轉換為提示符方式
-------------
/*+
begin_outline_data
use_nl(@"sel$1" "d"@"sel$1") --使用use_nl提示，即嵌套循環
leading(@"sel$1" "e"@"sel$1" "d"@"sel$1") --指明前導表
index_rs_asc(@"sel$1" "d"@"sel$1" ("dept"."deptno")) --指明對于d上的通路方式為使用索引
full(@"sel$1" "e"@"sel$1") --指明對于e上的通路方式為全表掃描
outline_leaf(@"sel$1")
all_rows
optimizer_features_enable('10.2.0.3')
ignore_optim_embedded_hints
end_outline_data
*/
predicate information (identified by operation id): --謂詞資訊部分，在執行計劃中id帶有星号的每一行均對應到下面中的一行
---------------------------------------------------
2 - filter("e"."ename"='smith')
4 - access("e"."deptno"="d"."deptno")
column projection information (identified by operation id): --執行時每一步驟所傳回的列，下面的不同步驟傳回了不同的列
-----------------------------------------------------------
1 - (#keys=0) "e"."empno"[number,22], "e"."ename"[varchar2,10],
"e"."job"[varchar2,9], "e"."mgr"[number,22], "e"."hiredate"[date,7],
"e"."sal"[number,22], "e"."comm"[number,22], "e"."deptno"[number,22],
"d"."deptno"[number,22], "d"."dname"[varchar2,14], "d"."loc"[varchar2,13]
2 - "e"."empno"[number,22], "e"."ename"[varchar2,10], "e"."job"[varchar2,9],
"e"."mgr"[number,22], "e"."hiredate"[date,7], "e"."sal"[number,22],
"e"."comm"[number,22], "e"."deptno"[number,22]
3 - "d"."deptno"[number,22], "d"."dname"[varchar2,14], "d"."loc"[varchar2,13]
4 - "d".rowid[rowid,10], "d"."deptno"[number,22]
note --注釋與描述部分，下面的描述中給出了本次sql語句使用了動态采樣功能
-----
- dynamic sampling used for this statement
58 rows selected.
2、實際執行計劃中的各字段與子產品
sql> select /*+ gather_plan_statistics */ * --注意此處增加了提示gather_plan_statistics并且該語句被執行
2 from emp e,dept d
7369 smith clerk 7902 17-dec-80 800 20 20 research dallas
sql> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); --使用display_cursor擷取實際的執行計劃
sql_id fpx7zw59f405d, child number 0 --這部分給出了sql語句的sql_id,子遊标号以及原始的sql語句
-------------------------------------
select /*+ gather_plan_statistics */ * from emp e,dept d where e.deptno=d.deptno and
e.ename='smith'
plan hash value: 351108634 --sql 語句的哈希值
--sql 語句的執行計劃，可以看到下面顯示的字段一部分不同于預估執行計劃中的字段
----------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers | reads |
| 1 | nested loops | | 1 | 1 | 1 |00:00:00.01 | 10 | 1 |
|* 2 | table access full | emp | 1 | 1 | 1 |00:00:00.01 | 8 | 0 |
| 3 | table access by index rowid| dept | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 4 | index unique scan | pk_dept | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
predicate information (identified by operation id):
note
26 rows selected.
-- 總結
-- 由上可知在不同的情形下可以獲得執行計劃的不同資訊，而不同資訊則展現了sql語句對應的不同情況，是以應根據具體的情形具體分析。
執行計劃各種掃描釋義
sort group by
table access full
-- oracle會讀取表中所有的行，并檢查每一行是否滿足sql語句中的 where 限制條件；
-- 全表掃描時可以使用多塊讀（即一次i/o讀取多塊資料塊）操作，提升吞吐量；
-- 使用建議：資料量太大的表不建議使用全表掃描，除非本身需要取出的資料較多，占到表資料總量的 5% ~ 10% 或以上
table access by rowid
-- rowid是由oracle自動加在表中每行最後的一列僞列，既然是僞列，就說明表中并不會實體存儲rowid的值；
-- 你可以像使用其它列一樣使用它，隻是不能對該列的值進行增、删、改操作；
-- 一旦一行資料插入後，則其對應的rowid在該行的生命周期内是唯一的，即使發生行遷移，該行的rowid值也不變。
-- 讓我們再回到 table access by rowid 來：
-- 行的rowid指出了該行所在的資料檔案、資料塊以及行在該塊中的位置，是以通過rowid可以快速定位到目标資料上，這也是oracle中存取單行資料最快的方法；
table access by index scan
-- 在索引塊中，既存儲每個索引的鍵值，也存儲具有該鍵值的行的rowid。
-- 索引掃描其實分為兩步：
-- Ⅰ：掃描索引得到對應的rowid
-- Ⅱ：通過rowid定位到具體的行讀取資料
-- 索引掃描又分五種：
1、index unique scan
-- 針對唯一性索引（unique index）的掃描，每次至多隻傳回一條記錄；
-- 表中某字段存在 unique、primary key 限制時，oracle常實作唯一性掃描；
2、index range scan
-- 使用一個索引存取多行資料；
-- 發生索引範圍掃描的三種情況：
-- 在唯一索引列上使用了範圍操作符（如：> < <> >= <= between）
-- 在組合索引上，隻使用部分列進行查詢（查詢時必須包含前導列，否則會走全表掃描）
-- 對非唯一索引列上進行的任何查詢
3、index full scan
-- 進行全索引掃描時，查詢出的資料都必須從索引中可以直接得到（注意全索引掃描隻有在cbo模式下才有效）
-----------------------------------------------------------
-- oracle優化器簡述
-- oracle中的優化器是sql分析和執行的優化工具，它負責生成、制定sql的執行計劃。
-- oracle的優化器有兩種：
-- rbo（rule-based optimization） 基于規則的優化器
-- cbo（cost-based optimization） 基于代價的優化器
rbo：
-- rbo有嚴格的使用規則，隻要按照這套規則去寫sql語句，無論資料表中的内容怎樣，也不會影響到你的執行計劃；
-- 換句話說，rbo對資料“不敏感”，它要求sql編寫人員必須要了解各項細則；rbo一直沿用至oracle 9i，從oracle 10g開始，rbo已經徹底被抛棄。
cbo：
cbo是一種比rbo更加合理、可靠的優化器，在oracle 10g中完全取代rbo；
cbo通過計算各種可能的執行計劃的“代價”，即cost，從中選用cost最低的執行方案作為實際運作方案；
它依賴資料庫對象的統計資訊，統計資訊的準确與否會影響cbo做出最優的選擇，也就是對資料“敏感”。
4、index fast full scan
-- 掃描索引中的所有的資料塊，與 index full scan 類似，但是一個顯著的差別是它不對查詢出的資料進行排序（即資料不是以排序順序被傳回）
5、index skip scan
-- oracle 9i後提供，有時候複合索引的前導列（索引包含的第一列）沒有在查詢語句中出現，oralce也會使用該複合索引，這時候就使用的index skip scan;
-- 什麼時候會觸發 index skip scan 呢？
-- 前提條件：表有一個複合索引，且在查詢時有除了前導列（索引中第一列）外的其他列作為條件，并且優化器模式為cbo時
-- 當oracle發現前導列的唯一值個數很少時，會将每個唯一值都作為正常掃描的入口，在此基礎上做一次查找，最後合并這些查詢
-- 例如：
-- 假設表emp有ename（雇員名稱）、job（職位名）、sex（性别）三個字段，并且建立了如 create index idx_emp on emp (sex, ename, job) 的複合索引；
-- 因為性别隻有 '男' 和 '女' 兩個值，是以為了提高索引的使用率，oracle可将這個複合索引拆成 ('男', ename, job)，('女', ename, job) 這兩個複合索引；
-- 當查詢 select * from emp where job = 'programmer' 時，該查詢發出後：
-- oracle先進入sex為'男'的入口，這時候使用到了 ('男', ename, job) 這條複合索引，查找 job = 'programmer' 的條目；
-- 再進入sex為'女'的入口，這時候使用到了 ('女', ename, job) 這條複合索引，查找 job = 'programmer' 的條目；
-- 最後合并查詢到的來自兩個入口的結果集。
-- nested loops … 描述的是表連接配接方式；
-- join 關鍵字用于将兩張表作連接配接，一次隻能連接配接兩張表，join 操作的各步驟一般是串行的（在讀取做連接配接的兩張表的資料時可以并行讀取）；
-- 表（row source）之間的連接配接順序對于查詢效率有很大的影響，對首先存取的表（驅動表）先應用某些限制條件（where過濾條件）以得到一個較小的row source，
-- 可以使得連接配接效率提高。
-- 驅動表（driving table）與比對表（probed table）
驅動表（driving table）：
-- 表連接配接時首先存取的表，又稱外層表（outer table），這個概念用于 nested loops（嵌套循環） 與 hash join（哈希連接配接）中；
-- 如果驅動表傳回較多的行資料，則對所有的後續操作有負面影響，故一般選擇小表（應用where限制條件後傳回較少行數的表）作為驅動表。
比對表（probed table）：
-- 又稱為内層表（inner table），從驅動表擷取一行具體資料後，會到該表中尋找符合連接配接條件的行。故該表一般為大表（應用where限制條件後傳回較多行數的表）。
-- 表連接配接的幾種方式：
sort merge join （排序-合并連接配接）
nested loops （嵌套循環）
hash join （哈希連接配接）
cartesian product （笛卡爾積）
-- 注：這裡将首先存取的表稱作 row source 1，将之後參與連接配接的表稱作 row source 2；
1、sort merge join
-- 假設有查詢：
select a.name, b.name from table_a a join table_b b on (a.id = b.id)
-- 内部連接配接過程：
-- a) 生成 row source 1 需要的資料，按照連接配接操作關聯列（如示例中的a.id）對這些資料進行排序
-- b) 生成 row source 2 需要的資料，按照與 a) 中對應的連接配接操作關聯列（b.id）對資料進行排序
-- c) 兩邊已排序的行放在一起執行合并操作（對兩邊的資料集進行掃描并判斷是否連接配接）
-- 延伸：
-- 如果示例中的連接配接操作關聯列 a.id，b.id 之前就已經被排過序了的話，連接配接速度便可大大提高，因為排序是很費時間和資源的操作，尤其對于有大量資料的表。
-- 故可以考慮在 a.id，b.id 上建立索引讓其能預先排好序。不過遺憾的是，由于傳回的結果集中包括所有字段，是以通常的執行計劃中，即使連接配接列存在索引，
-- 也不會進入到執行計劃中，除非進行一些特定列處理（如僅僅隻查詢有索引的列等）。
-- 排序-合并連接配接的表無驅動順序，誰在前面都可以；
-- 排序-合并連接配接适用的連接配接條件有： < <= = > >= ，不适用的連接配接條件有： <> like
2、nested loops
-- a) 取出 row source 1 的 row 1（第一行資料），周遊 row source 2 的所有行并檢查是否有比對的，取出比對的行放入結果集中
-- b) 取出 row source 1 的 row 2（第二行資料），周遊 row source 2 的所有行并檢查是否有比對的，取出比對的行放入結果集中
-- c) ……
-- 若 row source 1 （即驅動表）中傳回了 n 行資料，則 row source 2 也相應的會被全表周遊 n 次。
-- 因為 row source 1 的每一行都會去比對 row source 2 的所有行，是以當 row source 1 傳回的行數盡可能少并且能高效通路 row source 2（如建立适當的索引）時，效率較高。
-- 嵌套循環的表有驅動順序，注意選擇合适的驅動表。
-- 嵌套循環連接配接有一個其他連接配接方式沒有的好處是：可以先傳回已經連接配接的行，而不必等所有的連接配接操作處理完才傳回資料，這樣可以實作快速響應。
-- 應盡可能使用限制條件（where過濾條件）使驅動表（row source 1）傳回的行數盡可能少，同時在比對表（row source 2）的連接配接操作關聯列上建立唯一索引（unique index）
-- 或是選擇性較好的非唯一索引，此時嵌套循環連接配接的執行效率會變得很高。若驅動表傳回的行數較多，即使比對表連接配接操作關聯列上存在索引，連接配接效率也不會很高。
3、hash join（
-- 哈希連接配接隻适用于等值連接配接（即連接配接條件為 = ）
-- hash join對兩個表做連接配接時并不一定是都進行全表掃描，其并不限制表通路方式；
-- 内部連接配接過程簡述：
-- a) 取出 row source 1（驅動表，在hash join中又稱為build table） 的資料集，然後将其建構成記憶體中的一個 hash table（hash函數的hash key就是連接配接操作關聯列），建立hash位圖（bitmap）
-- b) 取出 row source 2（比對表）的資料集，對其中的每一條資料的連接配接操作關聯列使用相同的hash函數并找到對應的 a) 裡的資料在 hash table 中的位置，在該位置上檢查能否找到比對的資料
-- hash table相關
-- 散列（hash）技術：
-- 在記錄的存儲位置和記錄具有的關鍵字key之間建立一個對應關系 f ，使得輸入key後，可以得到對應的存儲位置 f(key)，這個對應關系 f 就是散列（哈希）函數；
-- 采用散列技術将記錄存儲在一塊連續的存儲空間中，這塊連續的存儲空間就是散清單（哈希表）；
-- 不同的key經同一散列函數散列後得到的散列值理論上應該不同，但是實際中有可能相同，相同時即是發生了散列（哈希）沖突，解決散列沖突的辦法有很多，
-- 比如hashmap中就是用鍊位址法來解決哈希沖突；
-- 哈希表是一種面向查找的資料結構，在輸入給定值後查找給定值對應的記錄在表中的位置以擷取特定記錄這個過程的速度很快。
-- hash join的三種模式：
optimal hash join
onepass hash join
multipass hash join
1、optimal hash join：
-- optimal 模式是從驅動表（也稱build table）上擷取的結果集比較小，可以把根據結果集建構的整個hash table都建立在使用者可以使用的記憶體區域裡。optimal_hash_join
-- 連接配接過程簡述：
-- Ⅰ：首先對build table内各行資料的連接配接操作關聯列使用hash函數，把build table的結果集建構成記憶體中的hash table。如圖所示，可以把hash table看作記憶體中的一塊大的方形區域，
-- 裡面有很多的小格子，build table裡的資料就分散分布在這些小格子中，而這些小格子就是hash bucket。
-- Ⅱ：開始讀取比對表（probed table）的資料，對其中每行資料的連接配接操作關聯列都使用同上的hash函數，定位build table裡使用hash函數後具有相同值資料所在的hash bucket。
-- Ⅲ：定位到具體的hash bucket後，先檢查bucket裡是否有資料，沒有的話就馬上丢掉比對表（probed table）的這一行。
-- 如果裡面有資料，則繼續檢查裡面的資料（驅動表的資料）是否和比對表的資料相比對。
2、onepass hash join :
-- 從驅動表（也稱build table）上擷取的結果集較大，無法将根據結果集建構的hash table全部放入記憶體中時，會使用 onepass 模式。one_pass_hash_join
-- Ⅰ：對build table内各行資料的連接配接操作關聯列使用hash函數，根據build table的結果集建構hash table後，由于記憶體無法放下所有的hash table内容，
-- 将導緻有的hash bucket放在記憶體裡，有的hash bucket放在磁盤上，無論放在記憶體裡還是磁盤裡，oracle都使用一個bitmap結構來反映這些hash bucket的狀态（包括其位置和是否有資料）。
-- Ⅱ：讀取比對表資料并對每行的連接配接操作關聯列使用同上的hash函數，定位bitmap上build table裡使用hash函數後具有相同值資料所在的bucket。
-- 如果該bucket為空，則丢棄比對表的這條資料。如果不為空，則需要看該bucket是在記憶體裡還是在磁盤上。
-- 如果在記憶體中，就直接通路這個bucket并檢查其中的資料是否比對，有比對的話就傳回這條查詢結果。
-- 如果在磁盤上，就先把這條待比對資料放到一邊，将其先暫存在記憶體裡，等以後積累了一定量的這樣的待比對資料後，再批量的把這些資料寫入到磁盤上。
-- Ⅲ：當把比對表完整的掃描了一遍後，可能已經傳回了一部分比對的資料了。接下來還有hash table中一部分在磁盤上的hash bucket資料以及比對表中部分被寫入到磁盤上
-- 的待比對資料未處理，現在oracle會把磁盤上的這兩部分資料重新比對一次，然後傳回最終的查詢結果。
3、multipass hash join：
-- 當記憶體特别小或者相對而言hash table的資料特别大時，會使用 multipass 模式。multipass會多次讀取磁盤資料，應盡量避免使用該模式。
-- … outer 描述的是表連接配接類型；
-- 表連接配接的兩種類型：
inner join （内連接配接）
outer join （外連接配接）
-- 示例資料說明：
-- 現有a、b兩表，a表資訊如下：
table_a
id name
---------------------
1 小明
2 小李
3 小紅
4 小劉
-- b表資訊如下：
table_b
2 小新
3 小美
5 小琳
6 小麗
-- 下面的例子都用a、b兩表來示範。
1、inner join
-- 隻傳回兩表中相比對的記錄。
-- inner join 又分為兩種：
-- 等值連接配接（連接配接條件為 = ）
-- 非等值連接配接（連接配接條件為 非 = ，如 > >= < <= 等）
-- 等值連接配接用的最多，下面以等值連接配接舉例：
-- 内連接配接的兩種寫法：連接配接時隻傳回滿足連接配接條件（a.id = b.id）的記錄
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a inner join b b on (a.id = b.id)
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a join b b on (a.id = b.id)
2、outer join
-- outer join 分為三種：
left outer join （可簡寫為 left join，左外連接配接）
right outer join（ right join，右外連接配接）
full outer join （ full join，全外連接配接）
-- a) left join
-- 傳回的結果不僅包含符合連接配接條件的記錄，還包含左邊表中的全部記錄。（若傳回的左表中某行記錄在右表中沒有比對項，則右表中的傳回列均為空值）
-- 兩種寫法：
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a left outer join b b on (a.id = b.id)
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a left join b b on (a.id = b.id)
-- b) right join（右連接配接）：
-- 傳回的結果不僅包含符合連接配接條件的記錄，還包含右邊表中的全部記錄。（若傳回的右表中某行記錄在左表中沒有比對項，則左表中的傳回列均為空值）
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a right outer join b b on (a.id = b.id)
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a right join b b on (a.id = b.id)
-- c) full join（全連接配接）：
-- 傳回左右兩表的全部記錄。(左右兩邊不比對的項都以空值代替)
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a full outer join b b on (a.id = b.id)
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a full join b b on (a.id = b.id)
-- (+) 操作符
-- (+) 操作符是oracle特有的表示法，用來表示外連接配接（隻能表示 左外、右外 連接配接），需要配合where語句使用。
-- 特别注意：(+) 操作符在左表的連接配接條件上表示右連接配接，在右表的連接配接條件上表示左連接配接。如：
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a, b b where a.id = b.id(+)
-- 實際與左連接配接 select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a left join b b on (a.id = b.id) 效果等價
select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a, b b where a.id(+) = b.id
-- 實際與右連接配接 select a.id a_id, a.name a_name, b.id b_id, b.name b_name from a a right join b b on (a.id = b.id) 效果等價
-- 總結 各連接配接方法試用的場景：
-- 排序-合并連接配接
對于非等值連接配接，這種方式效率比較高
如果在關聯列上有索引，效果更好
對于将兩個比較大的row source做連接配接，該連接配接方法比nl要好
如果sort merge傳回的row source過大，則又會使用過多的rowid在表中查詢資料時，資料庫性能下降，因為過多的i/o
-- 嵌套循環
如果外部表比較小，并且在内部表上有唯一索引，或者高選擇性索引
該方法有其它連接配接方法沒有的優點：可以先傳回已經連接配接的行，而不必等待所有的連接配接操作處理完才傳回資料，這樣可以實作快速的響應時間
-- 哈希連接配接
一般來說，其效率好于其它兩種連接配接，但這種連接配接隻能用在cbo優化中，而且需要設定合适的hash_area_size參數
隻用于等值連接配接
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)