树形查找也疯狂&优化措施

一个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