樹形查詢也瘋狂&優化措施

樹形查詢也瘋狂&優化措施

一個procedure執行了3個小時還沒有結束，通過procedure的執行log，定位到是一個樹形查詢語句造成

的(前臺程式的異常，導致了一些奇怪的記錄產生，雖然這樣的記錄量很少，卻讓oracle的樹形查詢語句一

直瘋狂，不知疲倦的執行)，現在大致模擬一下當時的情況，在只有16條記錄的表中做樹形查詢，樹形查

找也會瘋狂。

create table tree_query(id number,pid number,name varchar2(100));

insert into tree_query(id,pid,name) values (1,1,'one');

insert into tree_query(id,pid,name) values (1,1,'two');

insert into tree_query(id,pid,name) values (1,1,'three');

insert into tree_query(id,pid,name) values (1,1,'four');

insert into tree_query(id,pid,name) values (1,1,'five');

insert into tree_query(id,pid,name) values (1,1,'six');

insert into tree_query(id,pid,name) values (1,1,'seven');

insert into tree_query(id,pid,name) values (1,1,'eight');

insert into tree_query(id,pid,name) values (1,1,'nine');

insert into tree_query(id,pid,name) values (1,1,'ten');

insert into tree_query(id,pid,name) values (1,1,'eleven');

insert into tree_query(id,pid,name) values (1,1,'twelve');

insert into tree_query(id,pid,name) values (1,1,'thirteen');

insert into tree_query(id,pid,name) values (1,1,'fourteen');

insert into tree_query(id,pid,name) values (1,1,'fifteen');

insert into tree_query(id,pid,name) values (1,1,'sixteen');

commit;

這裡id,pid,name作為查詢和限制的條件，注意:現在是模擬故障時的狀況,在這裡,16條記錄的id,pid都相等

(問題也就出在這裡),實際的應用中, 這樣的記錄多半是異常的，但如果你在程式處理中,沒有針對這樣的異

常做處理，那在用到樹形查詢的時候就要留一個心了。

跑一個樹形查詢的sql測試一下

3:39:08 PM SQL> Create Table tree_test Parallel Nologging As

2 Select Id,pid,Name,Level High,connect_by_root(Name) root_Name,connect_by_iscycle iscyc

3 From tree_query

4 Start With Name='one'

5 Connect By Nocycle Prior Id=pid

6 And Prior Name<>Name

7 ;

Create Table tree_test Parallel Nologging As

Select Id,pid,Name,Level High,connect_by_root(Name) root_Name,connect_by_iscycle iscyc

From tree_query

Start With Name='one'

Connect By Nocycle Prior Id=pid

And Prior Name<>Name

ORA-01013: user requested cancel of current operation

5:58:48 PM SQL>

N久過去了，都沒有結束，它還是在跑的,並沒有死在這裡，可以用更少一點(5條)記錄去測試. 實際上，

樹形查詢是按深度優先的規則去做樹的遍歷，在這裡會先產生一個level=16的樹的最左邊分支，對其中的

每個節點，又會繼續如此查詢下去，試圖在構建一個既高又寬的巨大無比的樹,結果會有N多記錄產生，程

序一直停在這裡瘋狂做查詢運算呢。

樹形查詢可以很方便地實現樹形層次查詢功能，但如用的不好，有的時候跑出來的效率也很低，下面列舉了一些可能的優化措施，尤其是大資料量下做樹形遍歷的優化措施

1)建立合適的索引，沒有合適的索引，不但查詢效率低下，甚至最終可能會使你的temp表空間爆滿

2)深入理解業務邏輯，儘可能多的過濾資料，尤其是在connect by處的限制，這裡的限制直接就是做分支

修剪，可以過濾掉很多不必要的資料

3)樹形遍歷和dml分離原則，也就是說，如果樹形遍歷的結果需要再做dml，建議先把樹形遍歷的結果存

放到中間表中，其後隊中間表再做dml操作。

4)儘可能的指定start with，如果不指定start with，則每個記錄都作為遍歷起始點去做查詢

5)分清楚併合理利用節點修剪和分支修剪，where子句的限制將會將節點刪除，但是其後代不會受到影響，

connect by中加上限制條件會將滿足條件的整個樹枝包括後代都刪除。

6)如果業務邏輯允許，可以利用關鍵字level去限制樹的高度，對於上面的情況，在和業務人員溝通後，限

制了樹的高度。

關於樹形查詢命令的詳細說明，可參閱

Oracle樹形查詢 http://space.itpub.net/10159839/viewspace-584603

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-606967/，如需轉載，請註明出處，否則將追究法律責任。

樹形查詢也瘋狂&優化措施

相關文章