Oracle 樹查詢 效能最佳化紀實(start with, connect by)
在專案中做許可權控制時,需要用組織階層來控制能夠訪問的資料,
比如A組織的人可以看到其下屬組織的人員資料,或者只有A組織是B組織上級的時候才有看B組織人員資料的權利。
根據需求需要構築DB的表結構,如下(ORG_RANK)
組織ID(PK) 上位組織ID
ORG_ID HIGH_ORG_ID
根據上面的結構,使用的樹查詢語句(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。
只要你頭腦裡自己描繪出一個樹型的組織結構,那麼你自然會想到方法二的執行速度會明顯比方法一塊,
方法二是逆行查詢,查到的資料量小。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2122023/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [轉載]使用Oracle樹形查詢應注意 - Start with / Connect byOracle
- ORACLE 樹形查詢(connect by...start with...)的應用(三)Oracle
- ORACLE 樹形查詢(connect by...start with...)的應用(一)Oracle
- Connect by實現樹查詢的妙用
- Oracle 樹操作(select…start with…connect by…prior)Oracle
- Oracle Start with ....Connect ByOracle
- 【--SQL基礎-->層次化查詢(START BY ... CONNECT BY PRIOR)】SQL
- START WITH and CONNECT BY in Oracle SQLOracleSQL
- oracle 樹查詢Oracle
- 【SQL 學習】層次化查詢之CONNECT BY 和 START WITHSQL
- oracle中connect by prior實現遞迴查詢Oracle遞迴
- oracle中start with connect by priorOracle
- MySQL查詢效能最佳化MySql
- oracle樹形查詢Oracle
- 樹型結構start with..........connect by prior
- 【效能最佳化】Oracle 效能最佳化:降低列值聚簇因子 提高查詢效率Oracle
- Oracle start with connect by PostgreSQL recursive cteOracleSQL
- 查詢oracle效能SQLOracleSQL
- oracle的查詢最佳化Oracle
- start with ... connect by
- oracle樹形選單查詢Oracle
- oracle中的遞迴sql查詢 connect by prior實現遞迴Oracle遞迴SQL
- start with connect by prior
- start with connect by 用法
- START WITH...CONNECT BY
- [轉載] Oracle:start with...connect by子句的用法Oracle
- Oracle中start with...connect by prior子句用法.docOracle
- connect by..start with..
- start with ...connect by --轉載
- 10g樹形查詢新特性CONNECT_BY_ROOT的9i實現方式
- 10g樹形查詢新特性CONNECT_BY_ISLEAF的9i實現方式
- 千萬級資料深分頁查詢SQL效能最佳化實踐SQL
- B樹(多路查詢樹)
- connect by 樹形查詢在評估cardinality時存在著問題
- Oracle臨時表最佳化查詢速度Oracle
- Oracle臨時表 最佳化查詢速度Oracle
- 多路查詢樹
- 平衡查詢樹