Bottom-up hierarchical root path query recipe in oracle

Oracle supports hierarchical querying since long and has continuously extended the set of available features, i.e. pseudo-columns, path generation, leaf sorting by name, etc. With 11gR2, Oracle even complements with the ANSI SQL standard approach: the CONNECT BY syntax for hierarchical querying that can now be replaced by the ANSI SQL standard Recursive Subquery Factoring clause, see Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it for more information.
However, talking about hierarchical querying with Oracle always implies a top-down traversion (see another post on that subject from a couple of years ago that did not provided a PL/SQL ready solution as Implementing bottom-up path traversal for hierarchical tables). You start (START WITH) at some root node(s) of your choice and dive into the branches and leaves (CONNECT BY) by connecting child-nodes to parent-nodes until the final leaf has been consumed (check CONNECT_BY_ISLEAF) or a certain LEVEL has been reached by filter. There is tons of examples out there showing this in action. Here is just some simple code example to start off with, employing SYS_CONNECT_BY_PATH, introducing root- to leaf-node path generation over the child-to-parent relationship between columns T.P_ID and T.ID.

Implementing bottom-up path traversal for hierarchical tables

Update: There is a newer and imho better approach available with Bottom-up hierarchical root path query recipe in oracle .

oracle supports hierarchical queries on tables by the start with and connect by clauses. this usually preassumes that the rows of some table are chained by a value in column dad_id of some row pointing to a value in column son_id in a parent row, where son_id also denotes the primary key in the table. the root rows of the tree being set up that way, furthermore have a dedicated value in column dad_id. for positive, evolving numbers (id’s ;-), one may just choose 0 here. so far, a typical hierarchical query for a (male) family tree may look like this: