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。

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

方法二是逆行查詢，查到的數據量小。

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

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。

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

方法二是逆行查詢，查到的數據量小。

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]