A点出发到各地的路径及其距离

求所有的可能路径及距离,样例数据如下：

[sql] view plaincopy

1. with bus as  
2. (  
3. select 1 id, 'A' dstart, 'B' dend, '120' distance from dual union all  
4. select 2 id, 'B' dstart, 'C' dend, '200' distance from dual union all  
5. select 3 id, 'C' dstart, 'E' dend, '180' distance from dual union all  
6. select 4 id, 'A' dstart, 'D' dend, '150' distance from dual union all  
7. select 5 id, 'D' dstart, 'M' dend, '300' distance from dual union all  
8. select 6 id, 'F' dstart, 'M' dend, '260' distance from dual  
9. )  
10. select * From bus;  

实现效果:

1. SUM_DISTANCE    PATH  
2. 120     A->B  
3. 320     A->B->C  
4. 500     A->B->C->E  
5. 150     A->D  
6. 450     A->D->M  
7. 200     B->C  
8. 380     B->C->E  
9. 180     C->E  
10. 300     D->M  
11. 260     F->M  

原始SQL代码：

1. with bus as  
2. (  
3. select 1 id, 'A' dstart, 'B' dend, '120' distance from dual union all  
4. select 2 id, 'B' dstart, 'C' dend, '200' distance from dual union all  
5. select 3 id, 'C' dstart, 'E' dend, '180' distance from dual union all  
6. select 4 id, 'A' dstart, 'D' dend, '150' distance from dual union all  
7. select 5 id, 'D' dstart, 'M' dend, '300' distance from dual union all  
8. select 6 id, 'F' dstart, 'M' dend, '260' distance from dual   
9. )  
10. select level, a.dstart, a.dend,   
11.        sys_connect_by_path(distance,'+')  sum_distance,  
12.        (select sum(b.distance) from bus b start with b.id=a.id connect by prior dstart =  dend  
13.        ),  
14.        CONNECT_BY_root(a.dstart)|| sys_connect_by_path(a.dend,'->')  path,  
15.        CONNECT_BY_ISLEAF leaf        
16. from bus a  
17. connect by prior dend =  dstart;  

一看这个SQL语句里有一个标量子查询，数据量少的时候看不出来问题，多的时候就会出现性能问题，因此利用dbms_aw.eval_number函数处理了树形函数的返回值,优化后的SQL代码 ：

1. with bus as  
2. (  
3. select 1 id, 'A' dstart, 'B' dend, '120' distance from dual union all  
4. select 2 id, 'B' dstart, 'C' dend, '200' distance from dual union all  
5. select 3 id, 'C' dstart, 'E' dend, '180' distance from dual union all  
6. select 4 id, 'A' dstart, 'D' dend, '150' distance from dual union all  
7. select 5 id, 'D' dstart, 'M' dend, '300' distance from dual union all  
8. select 6 id, 'F' dstart, 'M' dend, '260' distance from dual   
9. )  
10. select level, a.dstart, a.dend, ltrim(sys_connect_by_path(distance,'+'),'+') distance_expression,  
11.        dbms_aw.eval_number(ltrim(sys_connect_by_path(distance,'+'),'+'))  sum_distance,  
12.        CONNECT_BY_root(a.dstart)|| sys_connect_by_path(a.dend,'->')  path,  
13.        CONNECT_BY_ISLEAF leaf        
14. from bus a  
15. connect by prior dend =  dstart;