[轉載]oracle樹形查詢 start with connect by

標籤：複製   for   order   isp   產生   with   間接   use   menu   

一、簡介
　　在oracle中start with connect by(prior)用來對樹形結構的資料進行查詢。其中start with conditon 給出的是資料搜尋範圍, connect by後面給出了遞迴查詢的條件,prior 關鍵字表示父資料，prior 條件表示子資料需要滿足父資料的什麼條件。如下
start with id= ‘10001‘ connect by prior parent_id= id and prior num = 5
表示查詢id為10001，並且遞迴查詢parent_id=id,為5的記錄。
二、執行個體
　　1、構造資料

 1 -- 表結構 2 create table menu( 3  id varchar2(64) not null, 4  parent_id varchar2(64) not null, 5  name varchar2(100) not null, 6  depth number(2) not null, 7  primary key (id) 8 ) 9 10 -- 初始化資料11 -- 頂級菜單12 insert into menu values (‘100000‘, ‘0‘, ‘頂級菜單1‘, 1);13 insert into menu values (‘200000‘, ‘0‘, ‘頂級菜單2‘, 1);14 insert into menu values (‘300000‘, ‘0‘, ‘頂級菜單3‘, 1); 15 16 -- 父級菜單17 -- 頂級菜單1 直接子功能表18 insert into menu values (‘110000‘, ‘100000‘, ‘菜單11‘, 2);19 insert into menu values (‘120000‘, ‘100000‘, ‘菜單12‘, 2);20 insert into menu values (‘130000‘, ‘100000‘, ‘菜單13‘, 2);21 insert into menu values (‘140000‘, ‘100000‘, ‘菜單14‘, 2); 22 -- 頂級菜單2 直接子功能表23 insert into menu values (‘210000‘, ‘200000‘, ‘菜單21‘, 2);24 insert into menu values (‘220000‘, ‘200000‘, ‘菜單22‘, 2);25 insert into menu values (‘230000‘, ‘200000‘, ‘菜單23‘, 2); 26 -- 頂級菜單3 直接子功能表27 insert into menu values (‘310000‘, ‘300000‘, ‘菜單31‘, 2); 28 29 -- 菜單13 直接子功能表30 insert into menu values (‘131000‘, ‘130000‘, ‘菜單131‘, 3);31 insert into menu values (‘132000‘, ‘130000‘, ‘菜單132‘, 3);32 insert into menu values (‘133000‘, ‘130000‘, ‘菜單133‘, 3);33 34 -- 菜單132 直接子功能表35 insert into menu values (‘132100‘, ‘132000‘, ‘菜單1321‘, 4);36 insert into menu values (‘132200‘, ‘132000‘, ‘菜單1332‘, 4);37  

　　產生的菜單階層如下：
頂級菜單1
          菜單11
          菜單12
          菜單13
                    菜單131
                    菜單132
                              菜單1321
                              菜單1322
                    菜單133
          菜單14
頂級菜單2
          菜單21
          菜單22
          菜單23
頂級菜單3
          菜單31

　　2、SQL查詢

--prior放的左右位置決定了檢索是自底向上還是自頂向下. 左邊是自上而下（找子節點），右邊是自下而上（找父節點）--找父節點select * from menu start with id=‘130000‘ connect by id = prior parent_id;

　　

--找子節點節點-- (子節點)id為130000的菜單，以及130000菜單下的所有直接或間接子功能表(prior 在左邊， prior、parent_id(等號右邊)在右邊）select * from menu start with id=‘130000‘ connect by prior id =  parent_id  ;

　　

-- (父節點)id為1321的菜單，以及1321菜單下的所有直接或間接父菜單(prior、parent_id(等號左邊) 都在左邊)select * from menu start with id=‘132100‘ connect by prior parent_id = id;-- prior 後面跟的是(parent_id) 則是尋找父節點,prior後面跟的是(id)則是尋找子節點

　　

--根據菜單組分類統計每個菜單包含子功能表的個數select id, max(name) name, count(1) from menu group by idconnect by prior parent_id = idorder by id

　　

-- 查詢所有的葉子節點select t2.* from menu t2 where id not in(select t.parent_id from menu t) order by id;

　　

三、效能問題　　

 　　對於 start with connect by語句的執行，oracle會進行遞迴查詢，當資料量大的時候會產生效能相關問題。

--產生執行計畫explain plan for select * from menu start with id=‘132100‘ connect by prior parent_id = id;-- 查詢執行計畫select *  from  table( dbms_xplan.display);

　　語句執行計畫結果如下：

Plan hash value: 3563250490 ----------------------------------------------------------------------------------------------| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |              |     1 |   133 |     1   (0)| 00:00:01 ||*  1 |  CONNECT BY WITH FILTERING    |              |       |       |            |          ||   2 |   TABLE ACCESS BY INDEX ROWID | MENU         |     1 |   133 |     1   (0)| 00:00:01 ||*  3 |    INDEX UNIQUE SCAN          | SYS_C0018586 |     1 |       |     1   (0)| 00:00:01 ||   4 |   NESTED LOOPS                |              |       |       |            |          ||   5 |    CONNECT BY PUMP            |              |       |       |            |          ||   6 |    TABLE ACCESS BY INDEX ROWID| MENU         |     1 |   133 |     1   (0)| 00:00:01 ||*  7 |     INDEX UNIQUE SCAN         | SYS_C0018586 |     1 |       |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------    1 - access("ID"=PRIOR "PARENT_ID")   3 - access("ID"=‘132100‘)   7 - access("ID"=PRIOR "PARENT_ID") Note-----   - dynamic sampling used for this statement

[轉載]oracle樹形查詢 start with connect by