[Reprint]oracle tree-shaped query start with connect by

First, Introduction
In Oracle, start with connect by (prior) is used to query the data of the tree structure. Where start with Conditon gives the data search scope, the condition of the recursive query is given by the connect by, and the prior keyword represents the parent data, and the prior condition indicates what conditions the child data needs to satisfy the parent data. As follows
Start with id= ' 10001 ' connect by prior parent_id= ID and prior num = 5
Represents a record with a query ID of 10001 and a recursive query of Parent_id=id, 5.
Second, examples
1. Construction data

 1-Table Structure 2 CREATE TABLE menu (3 ID varchar2 () not NULL, 4 parent_id VARCHAR2 (max) not NULL, 5 name VARCHAR2 (+) not NULL, 6 depth number (2) not NULL, 7 primary KEY (ID) 8) 9 10--Initialization data 11--top-level menu insert INTO menu values (' 100000 ', ' 0 ', ' top menu 1 ', 1); INSERT into menu values (' 200000 ', ' 0 ', ' top Menu 2 ', 1); INSERT into menu values (' 300000 ', ' 0 ', ' top menu 3 ', 1); 15 16--parent Menu 17--top Menu 1 Direct submenu insert into menu values (' 110000 ', ' 100000 ', ' Menu One ', 2); INSERT into menu values (' 1200  00 ', ' 100000 ', ' Menu ', 2 ', insert into menu values (' 130000 ', ' 100000 ', ' Menu ', 2 '), insert into menu values (' 140000 ', ' 100000 ', ' menu 14 ', 2); 22--Top Menu 2 Direct submenu insert into menu values (' 210000 ', ' 200000 ', ' menu ', 2 '), insert into menu values (' 220000 ', ' 200000 ' , ' menu ', 2); INSERT into menu values (' 230000 ', ' 200000 ', ' menu 23 ', 2); 26--top Menu 3 Direct submenu insert into menu values (' 310000 ', ' 300000 ', ' menu 31 ', 2); 28 29--Menu 13 Direct submenu insert into menu values (' 131000 ', ' 130000 ', ' Menu 131 ', 3);To menu values (' 132000 ', ' 130000 ', ' Menu ", 3); INSERT into menu values (' 133000 ', ' 130000 ', ' Menu 133 ', 3); 33 34--Menu 132 Direct submenu insert into menu values (' 132100 ', ' 132000 ', ' menu 1321 ', 4); INSERT into menu values (' 132200 ', ' 132000 ', ' menu 1332   ', 4); 37

The resulting menu hierarchy is as follows:
Top Menu 1
Menu 11
Menu 12
Menu 13
Menu 131
Menu 132
Menu 1321
Menu 1322
Menu 133
Menu 14
Top Menu 2
Menu 21
Menu 22
Menu 23
Top Menu 3
Menu 31

2. SQL query

The left and right position of the--prior determines whether the retrieval is bottom-up or top-down. On the left is top-down (Find child nodes), right is bottom up (looking for parent node)--Find parent Node select * from menu start with id= ' 130000 ' connect by id = Prior parent_id;

　　

--Find the child node node--(child node) the menu with ID 130000, and all the direct or indirect submenus under the 130000 menu (prior on the left, prior, parent_id (right side of the equals sign) on the right) SELECT * from the menu start with Id= ' 130000 ' connect by Prior ID =  parent_id  ;

　　

--(parent node) the menu with ID 1321, and all direct or indirect parent menus under the 1321 menu (Prior, parent_id (left of equal sign) are on the left) select * from menu start with id= ' 132100 ' Connect by P Rior parent_id = id;--Prior followed by IS (parent_id) is to find the parent node, prior followed by (ID) is to find the child node

　　

---According to the menu group statistics each menu contains the number of sub-menus select ID, max (name) name, Count (1) from the menu group by Idconnect by prior parent_id = Idorder by ID

　　

--Query all leaf nodes select t2.* from menu T2 where ID not in (select t.parent_id from menu t) Order by ID;

　　

Third, performance problems

For the start with Connect by statement, Oracle performs a recursive query that produces performance-related issues when the volume of data is large.

--Generate execution Plan explain plan for SELECT * from menu start with id= ' 132100 ' connect by prior parent_id = id;--query Execution Plan SELECT *  F Rom  table (dbms_xplan.display);

The statement execution plan results are as follows:

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 F or this statement

[Reprint]oracle tree query start with connect by