Oracle start with connect by prior ... Recursive query

The start with connect by Prior is primarily used for data recursive queries of the B-tree structure type, giving any node in the B-tree structure type, traversing its final parent or child node.

--CREATE TABLECreate Tableprior_test (ParentID Number(Ten), SubID Number(Ten));--The field type is best used with number instead of VARCHAR2 because the test SQL needs to compare the ID--InsertInsert  intoPrior_testValues(1,2 );Insert  intoPrior_testValues(1,3 );Insert  intoPrior_testValues(2,4 );Insert  intoPrior_testValues(2,5 );Insert  intoPrior_testValues(3,6 );Insert  intoPrior_testValues(3,7 );Insert  intoPrior_testValues(5,8 );Insert  intoPrior_testValues(5,9 );Insert  intoPrior_testValues(7,Ten );Insert  intoPrior_testValues(7, One );Insert  intoPrior_testValues(Ten, A );Insert  intoPrior_testValues(Ten, - );

-- Select Select *  from Prior_test

-- SQL-1 Select  Level  from  with=7 by=  Prior parentidorder  by leveldesc

-- SQL-2 Select  Level  from  with =7 by Prior subid = ParentID Order  by  Level desc

SQL parsing:

Start with clause: Traverse start condition

Connect BY clause: Join condition

Keyword Prior,prior with the parent node column ParentID together, prior ParentID is the direction of the parent node traversal, prior with the child node column subid together, prior subid in the direction of the child node traversal.

ParentID, subid Two column who placed in the ' = ' before the matter, the key is the field behind the prior. (Compare above query statements SQL-1 and SQL-2)

ORDER BY clause: sort

Observe the result sets that are executed separately from the following SQL-3 and SQL-4:

-- SQL-3 Select  Level  from with =7  by = parentid Order  by leveldesc

-- SQL-4 Select  Level  from with =7  by = parentid Order  by leveldesc

Conclusion: Start with SubID is different from start with parentid result set.

Add a WHERE clause

-- SQL-5 Select  Level  from bb_test T where > 3  with =   prior parentid Order  by  Level desc

The SQL execution order is: Start with clause first, in the Connect by clause, and finally the WHERE clause!

Where is only the pruning of the tree structure and does not change the hierarchy of the tree.

Oracle start with connect by prior ... Recursive query