[IT Architecture] Starbucks 買咖啡 VS Design Pattern

昉間有不少文章和書針對在StarBucks買咖啡的過程比較起軟體開發的Design Pattern. Design Pattern對從事MIS的人員有點像烏托邦, 因為MIS的工作, 以老闆對時程的要求大概不會允許你從Design Pattern開始一步一步設計系統, 但是IT人員總有個夢希望在程式變成如蜘蛛網般難讀之前還是可以有一個機制去將需求概念化並用圖示的方式去呈現. 對Design Pattern在下懂得不多不過還是從幾篇文章針對這個主題整理出一些心得,希望對日後的系統分析有一些幫助, 下次去StarBucks喝咖啡除了聊天看小姐還多一些正經事可以做!

事情就從踏入Starbucks店門開始吧, 當你點完你的咖啡後, 拿到發票, 此時服務生會拿起咖啡杯並在上面做標示. 之後就將此咖啡杯放入等待序(Queue)中. 這個設計就是系統分析時所謂的分離(Decouple), 就是將處裡訂單的服務生和煮咖啡的服務生工作分開, 如此一來, 在客人變多時, 處裡訂單的服務生還是可以專心處裡訂單. 另外, 如果等待序(Queue)的單子太多, 也可以透過增加煮咖啡的服務生來加快處理速度. 這也是常見的非同步處理(asynchronous processing).

但非同步處理並不是沒有缺點, 最明顯的就是咖啡完成的順序未必和訂單順序相同. 例如當煮咖啡的服務生為加快速度可能將不同訂單中同樣的咖啡一起處理或者有些煮咖啡機器煮的時間較長, 服務生要特別考慮. 但問題是同一張訂單的咖啡還是要一起給人客啊, 這時Starbucks用的方式是用人客的姓來區隔. "來賓王先生你的咖啡好了", 這時, 王先生的訂單不管幾杯都會一起交給王先生.

問題又來了, 客人臨時換單或機器出問題, Starbucks如何處理呢? 一個最簡單的方式就是認賠殺出, 人客說她點的是熱的但做出來的是冷的, 很多時候服務生會再做一杯熱的給客人, 做錯的就倒掉. 還有一個方式就是重試, 客人說糖不夠就再加一點, 當然前提是這是一個可以重複的動作. 最後一個就是全部回到原點,一個情形就是訂單在還沒煮以前就取消, 這時當然就是退錢給客戶將交易退回(Rollback).

如果Starbucks是用傳統的方式, 點一杯做一杯付一杯的錢, 我們應該會看到Starbucks櫃檯前永遠大排長龍, 因為一段時間內可以服務的客戶數目會降低. 當然傳統的方式, 訂單出錯的方式降低很多, 客戶臨時換單或機器出狀況都比較容易處理.

生活中其實充滿一些同步也有一堆非同步的動作, 有時停下腳步好好觀察一下也許會有一些不同的收穫喔!

參考文件:

http://eaipatterns.com/docs/IEEE_Software_Design_2PC.pdf

http://www.enterpriseintegrationpatterns.com/ramblings/18_starbucks.html

http://www.springerlink.com/content/r72253p030411878/

[SQL Tune] Star Transformation

Star transformation 是在EDW(Enterprise Data Warehouse)中用來Join Table的常見方式. Star Transformation 和傳統的 Nested Loop/Hash Join/Merge Join 在運算邏輯上有很大的差異. 簡單的說, Star Transformation 利用 Bitmap Index 將 Dimension Table先做Join和Filter後在去存取 Fact Table, 藉以大量的減少IO.

但如同 Oracle 的很多功能, 在使用這些功能前, 相關的需求和系統設定, 也必須先研究清楚以避免執行時出現問題, 等程式上線後再踩到地雷可別怪ORACLE沒有先跟你講喔!

0. 準備工作
-- Fact Table
-- 此 Table 筆數大約是220,000筆
CREATE TABLE STAR_FACT
(
QTY Number(6),
Package Varchar2(40),
Customer VARCHAR2(20 BYTE),
Stage VARCHAR2(25 BYTE) NOT NULL
);

-- Dimension Table 1
-- 此 Table 筆數大約是30筆
CREATE TABLE STAR_DIM_1
(
Stage VARCHAR2(20 BYTE),
section VARCHAR2(25 BYTE) NOT NULL
);
-- Dimension Table 2
-- 此 Table 筆數大約是300筆
CREATE TABLE STAR_DIM_2
(
Cus_code VARCHAR2(20 BYTE),
Cus_locationVARCHAR2(25 BYTE) NOT NULL
);
-- Dimension Table 3
-- 此 Table 筆數大約是300筆
CREATE TABLE STAR_DIM_3
(
Pkg_PkgcodeVARCHAR2(20 BYTE),
Pkg_Lead_CountVARCHAR2(25 BYTE) NOT NULL
);
-- Gather statistics
exec dbms_stats.gather_table_stats('RPT','Star_Fact');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_1');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_2');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_3');

-- Add constraint/Primary key at Dimension Table
alter table RPT.Star_Dim_1 add constraint Star_dim_1_pk primary key (Stage);
alter table RPT.Star_Dim_2 add constraint Star_Dim_2_pk primary key (Cus_Code);
alter table RPT.Star_Dim_3 add constraint Star_Dim_3_pk primary key (Pkg_Pkgcode);

-- Create Bitmap index on dimension key at Fact Table
create bitmap index Star_Fact_Dim_1 on RPT.Star_Fact(Stage);
create bitmap index Star_Fact_Dim_2 on RPT.Star_Fact(Customer);
create bitmap index Star_Fact_Dim_3 on RPT.Star_Fact(Package);

-- Analyze Table & Index
analyze table Star_Fact compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_1 compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_2 compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_3 compute statistics for table for all indexes for all indexed columns;

1. 使用 非Star Transformations

* 我們就先用非 Star Transformation看看Oracle 會用什麼方是去處理?
先將用以下指令確認Oracle 不會使用 Star Transformation
alter session set star_transformation_enabled='false';

* 接下來執行以下SQL, 此SQL從客戶, 產品和站點中指定一些屬性在去Fact Table中計算數量.


select D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE,sum(F.qty)
from star_fact F, star_DIm_1 D1, star_DIM_2 D2, star_DIM_3 D3
where F.customer=D2.cus_code
and F.stage=D1.stage
and F.package=D3.PKG_PKGCODE
and D1.section='F'
and D3.PKG_LEAD_COUNT=96
group by D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE ;


* 執行結果如下


-- Execution Plan
Plan
SELECT STATEMENT CHOOSE Cost: 131 Bytes: 636 Cardinality: 12
8 SORT GROUP BY Cost: 131 Bytes: 636 Cardinality: 12
7 HASH JOIN Cost: 120 Bytes: 77,592 Cardinality: 1,464
1 TABLE ACCESS FULL RPT.STAR_DIM_2 Cost: 2 Bytes: 5,915 Cardinality: 455
6 HASH JOIN Cost: 117 Bytes: 58,560 Cardinality: 1,464
2 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
5 HASH JOIN Cost: 114 Bytes: 1,558,890 Cardinality: 51,963
3 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
4 TABLE ACCESS FULL RPT.STAR_FACT Cost: 108 Bytes: 4,836,590 Cardinality: 219,845

-- AutoTrace
Description Value
sorts (memory) 3
sorts (disk) 0
redo size 0
recursive calls 0
physical reads 509
db block gets 0
consistent gets 1104
bytes sent via SQL*Net to client 1122
bytes received via SQL*Net from client 1036
SQL*Net roundtrips to/from client 4

從結果來看, 最終的筆數是6筆, Consistent Get 是 1104, 大概花了兩秒鍾. 而看看Execution Plan, 它用了三個 Hash Join, 先從Star_Dim_1去連接 Star_Fact, 後再接續連接 Star_Dim_2, Star_Dim3. 沒有意外, 這是正常 Oracle 處理此類SQL的做法.

2. Star Transformation 的原理和步驟

如前文所說, Star Transformation 可以有效的將 Dimension Table先做Join和Filter後再去存取 Fact Table, 其步驟如下:

* 為每一個 Dimension Table建立 Fact Table的 RowID的列表

- 先根據查詢的限制條件在每個 Dimension Table得到RowID列表.

- 利用前一步驟得到列表透過 Bitmap Index(Fact Table)去存取的Fact Table, 再將得到的
ROWID存成列表.

- 再來做的就是合併的動作. 假設SQL包含三個 Dimension key, 就重複前兩個步驟三次後,
將三個結果的 RowID作合併的動作.

* 執行過濾的動作, 所謂過濾就是說從前述合併的結果中判讀所有條件都成立的RowID,所謂所有條件就是說符合三的條件的資料.
* 將前述步驟的 RowID得到實際的資料.
* 在連接回 Dimension Table去得到SQL要的一些屬性值 .
* 對結果做加總.


3. Star Transformation 的實例演練

* 先將用以下指令確認Oracle 使用 Star Transformation

alter session set star_transformation_enabled='false';

* 執行同樣的SQL

select D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE,sum(F.qty)
from star_fact F, star_DIm_1 D1, star_DIM_2 D2, star_DIM_3 D3
where F.customer=D2.cus_code
and F.stage=D1.stage
and F.package=D3.PKG_PKGCODE
and D1.section='F'
and D3.PKG_LEAD_COUNT=96
group by D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE ;


-- Execution Plan
Plan
SELECT STATEMENT CHOOSE Cost: 54 Bytes: 53 Cardinality: 1
18 SORT GROUP BY Cost: 54 Bytes: 53 Cardinality: 1
17 HASH JOIN Cost: 53 Bytes: 53 Cardinality: 1
15 HASH JOIN Cost: 50 Bytes: 40 Cardinality: 1
13 HASH JOIN Cost: 47 Bytes: 1,320 Cardinality: 44
1 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
12 TABLE ACCESS BY INDEX ROWID RPT.STAR_FACT Cost: 43 Bytes: 4,092 Cardinality: 186
11 BITMAP CONVERSION TO ROWIDS
10 BITMAP AND
5 BITMAP MERGE
4 BITMAP KEY ITERATION
2 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
3 BITMAP INDEX RANGE SCAN RPT.STAR_FACT_03
9 BITMAP MERGE
8 BITMAP KEY ITERATION
6 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
7 BITMAP INDEX RANGE SCAN RPT.STAR_FACT_02
14 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
16 TABLE ACCESS FULL RPT.STAR_DIM_2 Cost: 2 Bytes: 5,915 Cardinality: 455

-- AutoTrace
Description Value
sorts (memory) 2
sorts (disk) 0
redo size 0
recursive calls 0
physical reads 0
db block gets 0
consistent gets 0
bytes sent via SQL*Net to client 695
bytes received via SQL*Net from client 734
SQL*Net roundtrips to/from client 3

從結果來看, 最終的筆數是6筆, Consistent Get 是 695,比前例少了一半 時間也是. 而看看Execution Plan, 如同前文所說, 它將Dimension Table 儘可能先用 Bitmap Index 串聯後, 在去存取 Fact Table. 從 Consistent Get 大幅下降就知道效果相當明顯.

4. Star Transformation 的需求

* Fact & Dimensional Table建立 Index. 通常來說, Fact Table上的Diemension key會建立Bitmap Index. 不像前述的 Nested Loop/Hash Join/Merge Join 一次只會使用單一Index(同一 Table), Star Transformation 會儘可能使用所有可用的 Index.
* Fact & Dimensional Table的 primary & foreign key關係, 這不是必要 , 但是建議要有. 針對資料量較大的 Table, 建立 primary & foreign key關係對資料 Load一定會有影響, 要自行斟酌影響程度做適當動作(如 Load 資料時, disable primary & foreign key關係)
* Database 設定
- Metalink 有一些 Star Transformation 的 Bug 說明. 這一點倒是蠻令人失望, 因為一堆 Oracle的新功能總是隱藏一堆BUG, 而這些BUG都要歷經一段時間被發現後, 在待 Oracle原廠出 Patch解決.
- 設定參數: Star_Transformation_Enabled, 設為 True後, optimizer才會考慮 Star Transformation.
- 設定參數: Hash_Join_Enabled, Hash Join會是 Star Transformation常用的 Join.
- 調整 PGA 參數讓 Star Transformation 儘量執行在 PGA 中.
* 還有一個參數 alter session set "_always_star_transformation"=true; 是 Oracle 的內部參數, 是要 Oracle Support 建議才能使用, 如果 Optimizer 不聽話堅持不run Star Transformation, 可以試試此參數.
* 總之, Star Transformation 是做 EDW一定要具備的技巧, 有這個工具, 許多大量資料存取的SQL變得簡單. 當然水可以載舟亦可覆舟, 在使用任何新功能別忘了周全的驗證一定是需要的.


參考文件

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/schemas.htm
So How Do Star Transformations Actually Works

[Google] Google Phone

Google Phone應該是2008年底最震撼資訊界的玩意, 一方面之前 Google Phone的開發有點神秘又感覺有點不樂觀, 另一方面適逢金融大海嘯, 這個新玩意對後市是否有 Killer Application的效應值得觀察. 身為 Google 的粉絲, Google phone 無疑是一大福音, 無論Google map, Google Calendar, Google Notebook,.. 都可以透過 Google Phone 的介面輕鬆存取是多麼愜意的事情啊! 可惜得是又如同 Wii, i-Phone, 台灣都不是第一個被考慮的銷售國, 真是的還虧Google Phone是由HTC所生產的耶, 真該跟Google抗議一下. 不過在商言商, Goolge有其考量, 我們也無從贅言!

聞香一下吧!

既然無福享用, 那就先爬個文, 研究研究順便和大家分享一下吧(老實說, 就算Google Phone現在引近我大概也無福消受, 因為荷包已被金融大海嘯吞噬的差不多空了.).

* 硬體
請參考以下連結, 原則上硬體或外型大概不是 Google Phone的強項.
http://tw.youtube.com/watch?v=UFN5cNpo7p4

* 軟體
Google Phone的作業系統就是開放平台(Android). 很多 Google相關的應用都可以在Google Phone上找到!
Picasa: 上傳圖片或看圖, 這是一定要得啊.
Google Talk: 愛瞭天的人有福啦,
YouTube: 影片也沒問題.
Google Earth: 哈哈,最重要的導航功能也在其中, Google 專有的實景更呈現其中, 光是這一點就可以把其他的導航手機給打給滿地找牙了.
Google Docs: 有這個玩意還要 Office 嗎, 用Google doc 文件都在Google 裡, 多方便.
Google reader for RSS
還有一些小東西, 搞不清楚再幹嘛

* 新功能和觀念
1. 手機相機刷barcode: 甚麼?? 無聊, 手機幹甚刷Barcode 啊? 這就是 Google 厲害的地方, 不用發明甚麼新東東, 它做的是把幾個不同的東東連結起來, 連接的介面就是它起家的搜尋引擎! 現在 Google 將手機 + Web + 商品上的Barcode + 搜尋 又引發消費者一項全新的應用領域. 想想以後拿到一本書, 看一半突然心有所想, 就拿著Google Phone刷一下書上的barcode, 電腦上就秀出該書的相關資訊順便把關於該書主題的其他書秀出來! 酷吧! 真是只有這些吃得好領得多的Google人才想得出來.

2. Google的另一個強項就是開放性, Google 將相關的手機應用程式API開放出來, 普羅大眾又可以自行開發新應用和大家分享, 如錦上添花般將Google Phone 推上雲端!

到這裡, 你知道 Google想賺的是你這隻手機的錢嗎? 錯!!! Google 要賺的還是廣告, 只是它讓廣告無所不入, 不僅滲透PC & NB 也要染指 Cellphone, 與其說Google 是高科技廠商不如說它是一家最賺錢的媒體業吧!

想擁有吧, 等等吧!! 看看好心的 Google何時想到 Taiwan吧?

參考文章
1. GIZMODO
2. FOXNEWS
3. http://www.google-phone.com/
4. 今週刊615 期

[Misc] 上 YouTube 學 Oracle

身處在資訊領域中, 卻從開始寫BLOG後才又瞭解到網路世界的進步, 為了尋找一些BLOG的題材無意間連結了YouTube, 發現不少有趣且實用的ORACLE教學用這麼生動的方式在介紹. 這其中有一大部分是以英文為主, 又多是廠商提供的, 但也有一些個人提供用POWERPOINT所拍攝. 舉凡 Oracle 新版本/功能介紹, SQL Tune, SQL Injection(有趣啊), Quest 的產品和功能(這個很多), 無所不包.

這裡就分享一下我這幾天在YouTube收集整理的播放清單, 有興趣的人可以再上 YouTube, 也許可以找到更多有趣的東東.

1. Oracle(Quest 提供的SQL Tune, PL/SQL Best Practice, Oracle News,..)
http://www.youtube.com/view_play_list?p=A59718855652CA03
2. SQL Injection
http://tw.youtube.com/share?p=691CD9BE5FF81D17

[PL/SQL] Logging Framework - Log 4 PL/SQL

相信JAVA的愛好者一定聽過Log4J這個Logging的架構(Framework). 程式開發和系統導入&使用的過程中, Logging是一個TroubleShooting必備的工具, 然而對PL/SQL而言, Oracle 並沒有提供相關的架構&功能, Logging大概都是自行開發的程式, 有將LOGGING模組化還是好的, 大部分我想是Hardcode,想記Log就記Log, 不同程式還記在不同TABLE,甚至高興還記在外部的Text file. 等到系統出問題要查LOG時才發現Log有記但會漏, 那時在跟老闆報告說找不到Bug在哪, 肯定被ㄞㄧ頓排頭!

所以Logging可以說是養兵千日用在一時的好幫手, 承平時候(User 沒有抱怨), Logging被視為是系統額外的負擔, 不管是程式執行時的Overhead 或 儲存空間的overhead. 但是一旦使用者抱怨來了發現資料有誤, 開發人員無論如何重建案發現場都無法模擬出這個BUG時, Logging 的資料又變成彌足珍貴. 這時一個Logging的底層(Framework)就可以幫助開發人員, 將這項工作綁在程式開發中又不會影響程式的架構. 一般而言, 一個好的LOGGING架構應該涵括以下幾點:
- 易於導入
- Log的紀錄必須能夠和程式邏輯的COMMIT/Rollback切隔開.(總不能程式一下Rollback指令連以記錄的LOG都Rollback掉)
- Log的紀錄必須能夠根據DEBUG的需求分成不同Level, 不同Level紀錄的LOG也不一樣.
- 系統的 Overhead 要低.

SourceForge 就有一項專案 Log 4 PL/SQL, 我們就來玩玩吧!!!

1. 安裝篇
自以下網頁 http://log4plsql.sourceforge.net/下載.後解壓縮, 照安裝說明執行批次檔然後就安裝成功, 哈哈, 別傻了, 基本上 open source的作者都是 Geek, 高手中的高手, 但這些Geek喜歡寫程式就是不喜歡寫文件. 所以最後還是把一堆 .SQL 檔案一個一個在TOAD中執行, 一個一個DEBUG才完成. 還好不算難裝啦.

2. 使用篇

-- 先試著呼叫 Log 4 PL/SQL 所建立的 Package(PLOG)的Procedure(Info).
Exec ULOG.PLOG.info ('mess info');

-- 第二個例子, 是著建立一個Procedure 在其中去呼叫-PLOG, 基本上要在哪個位置埋LOG, 要開發人員自己決定.
create or replace procedure TestProc is
cpt number;
begin
ulog.plog.info('this select raise ORA-01403:No Data Found');
select id into cpt from ulog.tlog where id = -1;
exception
when others then
ulog.plog.error; -- default message is SQLCODE SQLERRM
end;/

exec HR.TestProc

-- 最後看一下結果吧!
-- 從結果中, 可見記錄到的Error message.

select * from ulog.vlog;

ID	LDATE	LHSECS	LLEVEL	LSECTION	LTEXTE	LUSER
1	2009/1/8 下午 11:19:13	1121152	30	block.HR.TESTPROC	SQLCODE:100 SQLERRM:ORA-01403: 找不到資料	SYS

3. 進階篇

• 至於埋的LOG要不要作用就要看Logging level的設定, Logging level 有分以下幾種

  · isDebugEnabled

  · isInfoEnabled

  · isWarnEnabled

  · isErrorEnabled

  · isFatalEnabled

透過程式來控制什麼LEVEL要記什麼樣的 Log, 也就是將 IF - End If 加在塞LOG之前.

• 設定不一樣的啟動參數, 此工具有提供一些工具程式去改變參數, 如下
  pCTX PLOG.LOG_CTX := PLOG.init (pALERT => TRUE);

4. 架構篇

• Log 4 PL/SQL 可以紀錄LOG在以下標的物(Destination)

1. Table in Oracle Datablase
2. Oracle Datablase alert.log file
3. Oracle Datablase trace file
4. Standard output ·
5. By log4J::Log4JbackgroundProcess(JDBC, SMTP,...)
   

• 基本架構如下:

Log 4 PL/SQL 基本上是 Log 4J的延伸, 所以 Log4J的功能Log 4 PL/SQL 也可以使用.

[SQL Tune] 三個常見的JOIN簡介

談到SQL tune大概不能不談Join. 開發人員若在SQL語法中用了Join, 對ExecutionPlan中TABLE如何被Join一定要有基本的認識和瞭解, 否則Tune就只是空談而已. 本文就會針對Join的三種方式作一些介紹.

實例演練

我用的DATABASE是 Oracle XE 10G.

-- 以 HR 連線

Connect hr/hr;

-- 建立一個 Driving table

create table Driving_Emp as select * from EMPLOYEES;

-- 建立一個 Driven table
create table Driven_Dept as select * from DEPARTMENTS;

-- 建立 Index

create index e_deptno on Driven_Dept(DEPARTMENT_ID);
-- 收集相關 Statistics
exec dbms_stats.gather_table_stats('HR','Driving_Emp');

exec dbms_stats.gather_table_stats('HR','Driven_Dept');

1.Nested loop

這大概是最常見的Join方式, 以Join兩個TABLE為例, 基本上他的運作方式就像用VB寫兩個迴圈,各自抓取一個TABLE,一個是Driving TABLE(驅動端)一個是Driven TABLE(被驅動端). 前者是外迴圈後者是內迴圈. 如以下程式範例:

寫成SQL會是這樣:

Select Driving.*, Driven.* from Driving, Driven where Driving.col1=Driven.col2;

若寫成PL/SQL 應該會像這樣:

For i in (select * from Driving)

loop
For j in (select * from Driven where col2=i.col1)
loop
Display results;
End loop;

End loop;

這時ORACLE內部會做的是:

a) 找出外迴圈的driving table

b) 找出內迴圈的 driven table 指定給外迴圈

c) 根據每次外迴圈取得的資料, 連接內迴圈去取得相關資料.

使用時機

• 外迴圈最好有一些條件去限制回傳的筆數, 若外迴圈的筆數很多則Nested Loop的Cost可想 而知會相當高.
  
• 內迴圈連接外迴圈的欄位(Col2)最好有合適的INDEX, 否則內迴圈用Full Table Scan對效率可能會有影響.
• 原則上, Hash Join 的效率會較 Nested Loop 來得好.

實例演練
--利用以下指令虛擬出會產生 Nested Loop的資料筆數. 還蠻有趣的, 當筆數資料一調整就可以看到 Execution Plan從 Nested Loop 和 Hash Join之間跳來跳去.
exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driven_Dept', numrows => 40,numblks => 100 , avgrlen => 124);

exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driving_Emp', numrows => 100, numblks => 100, avgrlen => 124);

exec dbms_stats.set_index_stats(ownname => 'HR', indname => 'E_DEPTNO', numrows => 100, numlblks => 10);

-- Run 看看以下SQL
select
e.EMPLOYEE_ID,d.DEPARTMENT_NAME from Driving_Emp e, Driven_Dept d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

-- 果然跑出 Nested Loop
Plan
SELECT STATEMENT ALL_ROWSCost: 41 Bytes: 13,320 Cardinality: 360
4 TABLE ACCESS BY INDEX ROWID TABLE HR.DRIVEN_DEPT Cost: 1 Bytes: 120 Cardinality: 4
3 NESTED LOOPS Cost: 41 Bytes: 13,320 Cardinality: 360
1 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 29 Bytes: 700 Cardinality: 100
2 INDEX RANGE SCAN INDEX HR.E_DEPTNO Cost: 0 Cardinality: 9

2. Hash join

Hash joins 用來串接較大的TABLE, 可以把它想成改良版的Nested Loop, 有人稱它為Blocked Nested Loop, 可參閱 Wiki.

他的作法可分為兩個階段, 如下,

1. 建置 - Build : 建立一個 in-memory 的 hash table, 先將 Driving table 的一部分資料讀進Hash table(若 Memory 足夠則放在Memory, 若不夠則放在 Temporary space)透過 Hash Function 計算 Join Key. Hash的 Memory是屬於 Private memory, 也就是這個SQL所獨享的, 不像其他 Logic I/O 會用到 Latch. 相對於 Nested Loop 整個 Logic I/O 會大幅下降, 這也是 Hash Join 會優於 Nested Loop的原因之ㄧ.
   
2. 偵測 - Probe : 偵測第一階段的hash table的hash value去掃瞄Driven table, 若得到Output則輸出結果.
   

這時要看DBA 設定 Hash Join的相關參數, 若Memory設定沒有大到足以一次驟完成上述步驟, 則ORACLE會分多次完成. 和Nested Loop的差異在於前者是每次的外迴圈都要連接一次內迴圈而後者則是Hash Table讀滿後才連結一次內迴圈. 相較之下對CPU的需求也較高.

使用時機

• Driving table筆數少而Driven table 筆數多時, 此方式會較Nested Loop 來得有效率.
• 若是SQL的結果是要呈現在分頁的REPORT上面, Nested Loop 會好一些. 若是呈現全部的結果則 Hash Join 會強一些.

實例演練

-- 適著調大一下筆數囉
exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driving_Emp', numrows => 100000000, numblks => 100000, avgrlen => 124);

exec dbms_stats.set_index_stats(ownname => 'HR', indname => 'E_DEPTNO', numrows => 100000000, numlblks => 10000);

exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driven_Dept', numrows => 100000000,numblks => 10000 , avgrlen => 124);

-- Run SQL
select e.EMPLOYEE_ID,d.DEPARTMENT_NAME from Driving_Emp e, Driven_Dept d where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

-- 變成 Hash Join啦
Plan
SELECT STATEMENT ALL_ROWSCost: 15,127,941,348 Bytes: 33,636,363,300,000,000 Cardinality: 909,090,900,000,000
3 HASH JOIN Cost: 15,127,941,348 Bytes: 33,636,363,300,000,000 Cardinality: 909,090,900,000,000
1 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 33,028 Bytes: 700,000,000 Cardinality: 100,000,000
2 TABLE ACCESS FULL TABLE HR.DRIVEN_DEPT Cost: 5,551 Bytes: 3,000,000,000 Cardinality: 100,000,000

3. Sort merge join

Driving/Driven Table的觀唸到 Sort Merge Join 就不復見了. 原則上它的作法就是將兩個TABLE的資料各自排序後做合併的動作.原則上整個動作可以分為兩個部份.

• 排序 (Sort join operation)

從兩個TABLE中將資料各自做排序.

• 合併 (Merge join operation)

將排序完後的結果合併並篩選出結果.

使用時機

大概只有在join大概只有在join條件用到不等於(<, <=, >=, <>)時, 有機會期望用到此方式. 原則上,這是一個吃IO很重的動作.

實例演練

-- 如前所說 Merge Join常出現於'不等於', 那就來個不等於吧.

select *
from Driving_Emp e, Driving_Emp f
where E.EMPLOYEE_ID<>F.EMPLOYEE_ID
and E.HIRE_DATE<=F.hire_date

--是不是變成 Merge Join

Plan
SELECT STATEMENT ALL_ROWSCost: 11,824 Bytes: 71,327,102,832 Cardinality: 495,327,103
6 MERGE JOIN Cost: 11,824 Bytes: 71,327,102,832 Cardinality: 495,327,103
2 SORT JOIN Cost: 1,753 Bytes: 7,200,000 Cardinality: 100,000
1 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 35 Bytes: 7,200,000 Cardinality: 100,000
5 FILTER
4 SORT JOIN Cost: 1,753 Bytes: 7,200,000 Cardinality: 100,000
3 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 35 Bytes: 7,200,000 Cardinality: 100,000

相關連結

1.Expert one-on-one Oracle

2.Nested loops, Hash join and Sort Merge joins – difference?(Sachin Arora )

3. Wiki (Merge Join, Hash Join)

[Misc] Oracle 大師 (Guru)

起ORACLE, 幾位大師級的人物不能不認識 , 礙於語言限制, 大概僅限於英語系的大師( 兩岸三地稱得上大師的應該不多). 所謂大師應該指的不僅只是對ORACLE學有專精更需要的是要有很好的口語和文筆表達能力並且活躍於ORACLE社群中. 這幾位大師大多都有專屬的網站在分享他們的知識, ORACLE的愛好者應當常常光臨, 這裡就談談幾位我所熟悉的Guru吧!

1. Tom Kyte
網站 : http://asktom.oracle.com/
簡介 : 美國人, Oracle 的副總裁, 他所主持的網站用Oracle 自豪的APEX(以前叫 HTML DB)開發.
網站用問答的方式傳達ORACLE的知識, 他對ORACLE的功力之深可見一番. ORACLE的疑
難雜症, 到他的網站上GOOGLE一下, 就可找到他鞭闢入裡的解析, 更有陸籍網友將他的問答
整理成冊供人下載.
著作 : Oracle : Expert One to One, Effective Oracle by Design (現正貢在我的書櫃上)

2. Jonathan Lewis
網站 : http://www.jlcomp.demon.co.uk/
簡介 : 英國人, 現在是一家ORACLE顧問公司的負責人. 網站上提供類似精華區的ORACLE知識, 感
覺上比較是理論派的Guru. 他也是 OAK Table的一員(OAK Table是英國幾個ORACLE專家
組成類似讀書會, 也有專屬網站和出版物 --> Oracle Insights, Tables of Oak Table)
著作 : Practical Oracle 8i

3. Don Burleson
網站 : http://www.dba-oracle.com/
簡介 : 應該是老美吧, 一頭白髮是註冊商標. Burleson顧問公司提供ORACLE服務, 從顧問到DBA,
Tuning無所不包. 該公司更有不少ORACLE的出版物. 另外該公司也
開發了一些ORACLE的產品.他老兄的顧問費用多少錢勒, 請看, 注意是美元不是台幣呦!!
=============================================
Donald K. Burleson Rate
On-site Consulting $600 / hour
Remote Consulting $450 / hour
=============================================
著作 : 族繁不急備載

4. Mark Rittman
網站 : http://www.rittmanmead.com/
簡介 : 英國人, 負責Rittman Mead顧問公司, 專精於ORACLE的BI領域. 對ORACLE和BI的應用有
很多著墨. 很多Data Warehouse的觀念都是習自該網站. 網站上有很多文章可供下載!

5. Steven Feuerstein
網站 : http://www.oracleplsqlprogramming.com
簡介 : 應該是老美吧, 專精於PL/SQL, 於ORACLE雜誌中常闡述一些PLSQL的觀念. 現在應該是在QUEST(著名的IDE TOAD廠商)服務.
著作 : Oracle PL/SQL Programming , ORACLE PL/SQL BEST PRACTICES