hierarchy

what is a hierarchy?
I enjoy reading the wikipedia definition :

http://en.wikipedia.org/wiki/Hierarchy

In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship.

However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent.

This is no longer a practical relation.

Sterile variant :

SQL> select * from dept connect by 1=2;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Fertile variant :

SQL> select * from dept connect by 1=1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
...

the connect by does defines a true or a false connection. when true, everyone is your parent and everyone is your child. If false, you are the parent, but you have no child.

connect by level<5 in this mathematical approach is a way of describing an infinite connection, where the first ancestor can be at most your great-grandfather.

wow. quite challenging. Ironically, the doc does not describe that approach at all, and the "prior" is supposed to be mandatory. Let's wait to see if the doc get updated in a next release.