Use of connect by... start with... in Oracle

Connect... start... syntax: Java code select * from some_table [where condition 1] connect by [condition 2] start with [Condition 3]; the order of the connect by and start with statements does not affect the query results. [where condition 1] is not required. [Where condition 1], [condition 2], and [Condition 3] have different scopes: [condition 1] is used to filter records selected based on "connect by [condition 2] start with [Condition 3]". It is used to filter a single record, the structure of the tree is not considered; [condition 2] specifies the conditions for constructing the tree and the filtering conditions for the tree branches, the filter executed here filters out the records that meet the condition and all its subnodes. The [Condition 3] condition is used as the search start point, for top-down searches, the condition is defined as the root node, and for bottom-up searches, the condition is defined as the leaf node. Example: if there is a table with the following structure: some_table (id, p_id, name), where p_id stores the id of the parent record. Java code select * from some_table t where t. id! = 123 connect by prior t. p_id = t. id and t. p_id! = 321 start with t. p_id = 33 or t. p_id = 66; Description of prior: prior exists in [condition 2]. You can only find records that meet the "start with [Condition 3]" condition, the subnode will not be searching for these records. There are two possible methods to use: connect by prior t. p_id = t. id or connect by t. p_id = prior t. id. The previous method indicates that the top-down search method is used (first find the parent node and then find the child node ), the latter method uses the bottom-up search method (first finding the leaf node and then finding the parent node ). Ii. Execution principle connect... start... the execution principle can be described by executing the following program and calling the Stored Procedure RECURSE (): SQL code/* traversing each record in the table, compare whether the conditions after start with are met. If the conditions are not met, continue to the next one. If the conditions are met, use the record as the root node, and then call RECURSE () to recursively find the subnode under the node, this loop continues until all records in the entire table are traversed. */For rec in (select * from some_table) loop if FULLFILLS_START_WITH_CONDITION (rec) then RECURSE (rec, rec. child); end if; end loop; Stored procedure for finding sub-nodes: SQL code/* Stored procedure for finding sub-nodes */procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is begin APPEND_RESULT_LIST (rec);/* Add the record to the result set * // * re-traverse all records in the table and compare whether the conditions after connect by are met, if not, continue to the next one. If yes, use the record as the root node. Then call RECURSE () to Recursively search for the subnodes under the node, until the leaf node is found. */For rec_recurse in (select * from some_table) loop if substring (rec_recurse.child, new_parent) then RECURSE (rec_recurse, rec_recurse.child); end if; end loop; end procedure RECURSE; iii. Usage discussion from the above execution principle, we can see "connect... start... the method to construct a tree is as follows: (1) if the top-down method is used, each record in the table is used as the root node to generate a tree, therefore, the number of records in the table is how many trees are constructed. (2) if it is a bottom-up search method, each record in the table is used as a leaf node to generate branches. Therefore, the number of records in the table generates as many branches. Therefore, if the table does not strictly follow the principle that each record can have only one parent record, some records may exist in multiple trees, therefore, an exception may occur when multiple duplicate records are found.