Oracle’s START WITH and CONNECT BY clauses in the SELECT
statement automatically traverse a hierarchy. Without this feature, a complex
self-join would be required to identify which rows are logically related to
others. The START WITH clause identifies the row or rows to be considered the
starting points, or “roots,” of the hierarchy. The CONNECT BY PRIOR
clause then indicates how to identify which rows are related to each other.
For example, the query in
Listing A produces a “Reports To” listing from the EMPLOYEES
table in the HR sample schema provided by Oracle.
The LEVEL pseudocolumn indicates
how deeply the report is currently nested; here, I use it to LPAD the employee
names to indent them. The START WITH condition states that only employees 101
and 102 are to be considered as starting points. The CONNECT BY PRIOR clause
then links the employee_id column in one row to the manager_id column in the next, to indicate who reports to
whom.
If you run this query in the HR schema, you’ll notice that
the last names are not sorted within the listing for a specific manager; they
are listed in the order Oracle encountered them in processing the hierarchy.
If you want the subordinates in alphabetical order, you
might try to ORDER BY the original last_name column.
However, this would break up the hierarchy, and turn it back into a flat list
of names.
You might also try to ORDER BY the pseudocolumn
LEVEL first, which tells how deep a specific row is in the hierarchy. This,
too, breaks up the hierarchy—all the managers will be listed first, followed by
people who report to any of them.
In Oracle 10g (both releases), it’s now easy to do this: You
can use the new SIBLINGS keyword to create the correct ordering. The syntax is:
ORDER SIBLINGS BY <expression>
So adding the clause:
ORDER SIBLINGS BY last_name
to the end of the query will
preserve the hierarchy and also alphabetize the last names within each level.
Note that the original last_name was used not the
alias “Reports To.” The extra space padding in “Reports To”
would affect the sort, so the original must be used.
Listing B shows the output, both before and after adding ORDER
SIBLINGS BY.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob’s site.
