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

在項(xiàng)目中做權(quán)限控制時(shí)，需要用組織階層來控制能夠訪問的數(shù)據(jù)，
比如A組織的人可以看到其下屬組織的人員數(shù)據(jù)，或者只有A組織是B組織上級的時(shí)候才有看B組織人員數(shù)據(jù)的權(quán)利。
根據(jù)需求需要構(gòu)筑DB的表結(jié)構(gòu)，如下（ORG_RANK）

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

根據(jù)上面的結(jié)構(gòu)，使用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做性能評定時(shí)發(fā)現(xiàn)出現(xiàn)嚴(yán)重性能問題，（10層組織，3000條數(shù)據(jù)時(shí)）查詢時(shí)間1分多鐘，下面進(jìn)行了優(yōu)化。

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

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

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

上面的分析可以得到證明，因?yàn)檩斎氲箶?shù)第二層組織的執(zhí)行時(shí)間會(huì)比輸入最上層組織的執(zhí)行時(shí)間少的多。

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

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。

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

方法二是逆行查詢，查到的數(shù)據(jù)量小。

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

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

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

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

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

上面的分析可以得到證明，因?yàn)檩斎氲箶?shù)第二層組織的執(zhí)行時(shí)間會(huì)比輸入最上層組織的執(zhí)行時(shí)間少的多。

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

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。

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

方法二是逆行查詢，查到的數(shù)據(jù)量小。

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]