I. Introduction
In oracle, start with connect by (prior) is used to query tree-structured data. start with conditon gives the scope of data search. Conneby gives the condition of recursive query. Prior keyword denotes the parent data. Prior condition denotes what condition 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 that the query ID is 10001, and recursively queries parent_id=id for 5 records.
Two. Example
1. Constructing data

-- Table structure
create table menu(
 id varchar2(64) not null,
 parent_id varchar2(64) not null,
 name varchar2(100) not null,
 depth number(2) not null,
 primary key (id)
)

-- Initialization data
-- Top menu
insert into menu values ('100000', '0', 'Top-level menu 1', 1);
insert into menu values ('200000', '0', 'Top menu 2', 1);
insert into menu values ('300000', '0', 'Top menu 3', 1); 

-- Parent menu
-- Top Menu 1 Direct Submenu
insert into menu values ('110000', '100000', 'Menu 11', 2);
insert into menu values ('120000', '100000', 'Menu 12', 2);
insert into menu values ('130000', '100000', 'Menu 13', 2);
insert into menu values ('140000', '100000', 'Menu 14', 2); 
-- Top Menu 2 Direct Submenu
insert into menu values ('210000', '200000', 'Menu 21', 2);
insert into menu values ('220000', '200000', 'Menu 22', 2);
insert into menu values ('230000', '200000', 'Menu 23', 2); 
-- Top Menu 3 Direct Submenu
insert into menu values ('310000', '300000', 'Menu 31', 2); 

-- Menu 13 Direct Submenu
insert into menu values ('131000', '130000', 'Menu 131', 3);
insert into menu values ('132000', '130000', 'Menu 132', 3);
insert into menu values ('133000', '130000', 'Menu 133', 3);

-- Menu 132 Direct Submenu
insert into menu values ('132100', '132000', 'Menu 1321', 4);
insert into menu values ('132200', '132000', 'Menu 1332', 4);

The generated menu hierarchy is as follows:
Top-level 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

--prior The left and right position determines whether the search is bottom-up or top-down.. The left side is top-down (looking for child nodes) and the right side is bottom-up (looking for parent nodes).
--Finding parent nodes
select * from menu start with id='130000' connect by id = prior parent_id;

--Find child node
-- (Child node)id For 130000 menus and all direct or indirect submenus under 130000 menus(prior On the left, prior,parent_id(Equal sign right)On the right side)
select * from menu start with id='130000' connect by prior id =  parent_id  ;

-- (Parent node)id Menu 1321 and all direct or indirect parent menus under menu 1321(prior,parent_id(Equal sign left) All on the left.)
select * from menu start with id='132100' connect by prior parent_id = id;
-- prior Following is the(parent_id) Find the parent node,prior Following is the(id)Find child nodes

--Statistics of the number of submenus in each menu according to the menu group classification
select id, max(name) name, count(1) from menu 
group by id
connect by prior parent_id = id
order 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;

Performance issues

For the execution of the start with connect by statement, oracle will make recursive queries, which will cause performance-related problems when the amount of data is large.

Generate an execution plan
explain plan for select * from menu start with id='132100' connect by prior parent_id = id;

Query execution plan
select *  from  table( dbms_xplan.display);

The results of the statement execution plan 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):
---------------------------------------------------
- access("ID"=PRIOR "PARENT_ID")
- access("ID"='132100')
- access("ID"=PRIOR "PARENT_ID")
 
Note
-----
   - dynamic sampling used for this statement

Through the execution plan, we know that the change statement performs seven steps before the result set is queried and returned. When query conditions need to be filtered, we can optimize sql by looking at the execution plan.