The connect by prior recursive algorithm in Oracle

Source: Internet
Author: User

http://xxciof.blog.163.com/blog/static/7978132720095193113752/

The connect by prior recursive algorithm in Oracle

Oracle in Start With...connect by prior clause usageconnect 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;
Example:
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 so by indicating who the parent of each record is, a tree structure can be formed.
All records of this tree can be obtained by querying the above syntax.
Among them:
condition 1 is the limit of the root node, of course, you can relax the qualification to obtain multiple root nodes, is actually more than a tree.
Condition 2 is a join condition in which PRIOR represents the previous record, such as connect by PRIOR org_id = parent_id means that the org_id of the previous record is parent_id of this record, that is, the father of this record is the previous record.
Condition 3 is a filter condition that is used to filter all records returned.

The following is a brief introduction:
When you scan a tree structure table early, you need to access each node of the tree structure, one node can access only once, and the steps to access it are as follows:
The first step: starting from the root node;
The second step: Access the node;
The third step: to determine whether the node has no access to the child node, if any, then to its leftmost sub-section of the left, and perform the second step, otherwise perform the fourth step;
Fourth Step: If the node is the root node, the access is complete, otherwise, the fifth step;
Fifth step: Return to the parent node of the node and perform the third step.

in summary: The process of scanning the entire tree structure is also the process of traversing the tree in sequence.

1. Description of the tree structure
The tree structure data is stored in the table, and the hierarchical relationship between the data is the parent-child relationship, described by the relationship between columns and columns in the table, such as empno and MGR in the EMP table. Empno represents the employee's number, and Mgr represents 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 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.
use the Connect by friendly start with clause in the SELECT command to query the tree-structured relationships in the table. Its command format is as follows:
SELECT.....
CONNECT by {PRIORColumn Name 1 = column name 2| column name 1=prior split name2}
[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 will be linked to the tree-structured relationship. The priory operator must precede one of the two columns of a 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 side, thus determining whether the find tree structure is in the order of top-down or bottom-up. In connection relationships, column expressions are also allowed, in addition to the use of 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, but you can also specify multiple root nodes.
2. About thePRIOR
The prior operator is placed before and after the equals sign, determining the order in which the query is retrieved.
PRIORwhen placed in front of the equals sign in the Connect by clause, the sequential retrieval from the root node to the leaf node is forced, that is, from the parent node to the child node in the direction of the tree structure, which we call the top-down approach. such as:
CONNECT by PRIOR Empno=mgr
Piroroperator is placed after the equals sign in the Connect by clause, the sequential retrieval from the leaf node to the root node is enforced, that is, by the tree structure from the child node to the parent node, which we call the bottom-up approach. For example:
CONNECT by Empno=prior MGR
in this way, you should also specify a starting node.
3. Define a lookup Start node
when querying the tree structure from top to bottom, you can start looking down not only from the root node, but also from any node as the starting node. The result of this lookup is a branch of the tree that starts with that node.
4. UseLevel
In a table with a tree structure, each row of data is a node in the tree structure, and each row of records can have a layer number because the nodes are in different hierarchical locations. The layer number is determined by the distance between the node and the root node. The starting root node is always 1, the child node of the root node is 2, and so on, regardless of which node it starts from.
5. Cropping of nodes and branches
when querying a tree structure, you can remove some rows from the table, or you can cut out a branch in the tree, and use the WHERE clause to qualify a single node in the tree structure to remove a single node in the tree, but it does not affect its descendant nodes (when it is retrieved from the top down) or the predecessor node (when retrieving from the bottom).
6. Sort Display
As in other queries, you can also use the ORDER BY clause in a tree-structured query to change the order in which the results of the query are displayed, rather than in the order of traversing the tree structure

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.