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

4 thoughts on “hierarchy”

  1. >> I enjoy reading the wikipedia definition :

    I take it you will be expanding the “Hierarchies in programming” section to include the fruits of your research into hierarchies in Oracle.

    Cheers, APC

  2. Dubious as it is, this has been posted by Tom Kyte as a way of generating a series of integers (which you can then use as the basis of a data generator – I’ve used it to generate a date series).

    The PRIOR no longer appears to be mandatory.

    The question is whether it should behave in the way that it does, or whether this is a ‘feature’ – and as it’s not a documented behaviour, whether it’s safe to rely on it in code.
    (And certainly whether that should then be documented as how CONNECT BY behaves. It’s definitely how it behaves on Oracle 9.2 to 10g)

  3. what is a hierarchy? …
    In the doc the hierarchy is as a parent-child connection

    You really ought to correct that … documentation doesn’t say that is all there is to a hierarchy.

  4. > The PRIOR no longer appears to be mandatory
    well, either the doc is wrong, or there is a bug that should be detected one day…

    > documentation doesn’t say that is all there is to a hierarchy
    CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>