Oracle connect by prior recursive algorithm ____linux

In Oracle, start With...connect by prior clause usage
Connect by is used in structured queries, and its basic syntax is:
Select ... from tablename start with condition 1
Connect by Condition 2
Where Condition 3;

Cases:

SELECT * FROM table
Start with org_id = ' hbhqfwgwpy '
Connect by prior org_id = parent_id;

In short, a tree structure is stored in a table, for example, there are two fields in a list:
Org_id,parent_id then, by representing who the parent of each record is, a tree structure can be formed.
A query with the syntax above can get all the records of this tree.
which

Condition 1 is the limit statement of the root node, of course, we can relax the qualification to obtain multiple root nodes, in fact, many trees.
Condition 2 is a join condition in which the previous record is represented by PRIOR, such as connect by PRIOR org_id = parent_id that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.
Condition 3 is a filter condition that filters all returned records.

The brief introduction is as follows:

When you scan a tree structure table early, you need to access each node of the tree structure, one node can only be accessed once, and its access steps are as follows:
The first step: starting from the root node;
Step Two: Access the node;
The third step: to determine whether the node has no access to the child nodes, if so, then turn to its leftmost section of the inaccessible, and perform the second step, otherwise perform step fourth;
The fourth step: If the node is the root node, then the access is complete, otherwise perform the fifth step;
Step Fifth: Return to the parent node of the node and perform the third step.

In short: The process of scanning the entire tree structure is also the process of traversing the tree in the middle sequence.

1. Description of the tree structure
The data in the tree structure is stored in the table, and the hierarchical relationship between the data is the parent-child relationship, which is described by the relationship between the columns in the table and the columns, such as empno and MGR in the EMP table. Empno represents the number of the employee, MGR indicates the number of the person who led the employee, that is, the MGR value of the child node equals the Empno value of the parent node. In each row of the table, there is a MGR (outside the root node) that represents the parent node, and the entire tree structure can be determined through the parent node of each node. The
Use the Connect by affable start with clause in the SELECT command to query tree-structured relationships in the table. The command format is as follows:
SELECT ...
Connect by {PRIOR Column name 1 = column name 2| column name 1=prior 2}
[START with];
Where: The Connect by clause indicates that each row of data will be retrieved in a hierarchical order and that the data in the table is connected to the tree structure relationship. The priory operator must be placed in front of one of the two columns of the connection relationship. For parent-child relationships between nodes, the prior operator represents the parent node on one side and the child node on the other, determining whether the order of the lookup tree structure is top-down or bottom-up. In a connection relationship, you can use column expressions in addition to the column names. The START with clause is optional and is used to identify which node is the root node of the lookup tree structure. If the clause is omitted, all rows that satisfy the query criteria are used as the root node.

START with: Not only can you specify a root node, you can also specify multiple root nodes.
2. About Prior
The operator prior is placed at the position before and after the equals sign, determining the order in which the query is retrieved.
When Prior is placed in front of the equal sign in the Connect by clause, it is forced to retrieve the order from the root node to the leaf node, that is, by the parent node in the direction of the child node through the tree structure, which we call the Top-down approach. Such as:
CONNECT by PRIOR Empno=mgr
When the piror operator is placed after the equal sign in the Connect by clause, it forces the sequential retrieval from the leaf node to the root node, that is, the child node is directed to the parent node through the tree structure, which we call the bottom-up approach. For example:
CONNECT by Empno=prior MGR
You should also specify a starting node in this way.
3. Define Lookup Start Node
When querying the tree structure from top to bottom, not only can you start from the root node, you can also define any node as the starting node to start looking down. The result of this lookup is a branch of the structure tree that starts with the node.

4. Use level
In a table with a tree structure, each row of data is a node in the tree structure, so each row can have a layer number because the node is in a different hierarchical position. The layer number is determined based on the distance between the node and the root node. Regardless of which node starts, the start root node's layer number is always 1, the child node of the root node is 2, and so on. Figure 1.2 shows the hierarchy of the tree structure.

5. Cropping of nodes and branches
When querying a tree structure, you can remove some rows from the table, you can also cut off a branch in the tree, using the WHERE clause to qualify a single node in the tree structure to remove a single node from the tree, but it does not affect its descendant nodes (when it is retrieved from the top) or the predecessor node (when retrieved from the bottom).
6. Sort Display
As in other queries, you can also change the order in which query results are displayed in tree-structured queries, rather than in the order in which they are traversed in the tree structure.