Oracle 樹查詢 效能最佳化紀實（start with, connect by）

在項目中做許可權控制時，需要用組織階層來控制能夠訪問的資料，
比如A組織的人可以看到其下屬組織的人員資料，或者只有A組織是B組織上級的時候才有看B組織人員資料的權利。
根據需求需要構築DB的表結構，如下（ORG_RANK）

組織ID(PK) 上位組織ID
ORG_ID HIGH_ORG_ID

根據上面的結構，使用Oracle的樹查詢語句(start with和connect by)來建立SQL語句，如下：

查詢指定組織的直屬下層組織：

Sql代碼   
select  ORANK.ORG_ID  
  from  ORG_RANK ORANK  
where  ( level  - 1) = 1  
start with  ORANK.ORG_ID = #orgId#  
connect   by   prior  ORANK.ORG_ID = ORANK.HIGH_ORG_ID  
select ORANK.ORG_ID
  from ORG_RANK ORANK
where (level - 1) = 1
start with ORANK.ORG_ID = #orgId#
connect by prior ORANK.ORG_ID = ORANK.HIGH_ORG_ID對以上SQL做效能評定時發現出現嚴重性能問題，（10層組織，3000條資料時）查詢時間1分多鐘，下面進行了最佳化。

1、分析執行計畫，發現有Full Table，說明使用索引失敗，最佳化的方法是對HIGH_ORG_ID加上索引。

2、雖然只是查詢直屬下層的組織，但是上面SQL實際執行時，先查詢出指定組織的所有下層組織，

然後再從結果裡過濾出直屬下層的組織（where (level - 1) = 1）。

上面的分析可以得到證明，因為輸入倒數第二層組織的執行時間會比輸入最上層組織的執行時間少的多。

最佳化方法是增加connect by語句的條件（and (level - 1) <= 1），不滿足條件的子樹不會被查詢，會省去很多沒用的遞迴查詢。

Sql代碼   
select  ORANK.ORG_ID  
  from  ORG_RANK ORANK  
where  ( level  - 1) = 1  
start with  ORANK.ORG_ID = #orgId#  
connect   by   prior  ORANK.ORG_ID = ORANK.HIGH_ORG_ID  
and  ( level  - 1) <= 1  
select ORANK.ORG_ID
  from ORG_RANK ORANK
where (level - 1) = 1
start with ORANK.ORG_ID = #orgId#
connect by prior ORANK.ORG_ID = ORANK.HIGH_ORG_ID
and (level - 1) <= 1判斷組織A是組織B的上層組織：

方法一：查詢出A的所有下層組織，看其中是否有B；

方法二：查詢出B的所有上層組織，看其中是否有A。

只要你頭腦裡自己描繪出一個樹型的組織圖，那麼你自然會想到方法二的執行速度會明顯比方法一塊，

方法二是逆行查詢，查到的資料量小。