Oracle 樹查詢 性能優(yōu)化紀實（start with, connect by）

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

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

根據上面的結構，使用Oracle的樹查詢語句(start with和connect by)來創(chuàng)建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做性能評定時發(fā)現出現嚴重性能問題，（10層組織，3000條數據時）查詢時間1分多鐘，下面進行了優(yōu)化。

1、分析執(zhí)行計劃，發(fā)現有Full Table，說明使用索引失敗，優(yōu)化的方法是對HIGH_ORG_ID加上索引。

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

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

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

優(yōu)化方法是增加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。

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

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

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

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做性能評定時發(fā)現出現嚴重性能問題，（10層組織，3000條數據時）查詢時間1分多鐘，下面進行了優(yōu)化。

1、分析執(zhí)行計劃，發(fā)現有Full Table，說明使用索引失敗，優(yōu)化的方法是對HIGH_ORG_ID加上索引。

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

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

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

優(yōu)化方法是增加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。

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

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

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