Monthly Archives: July 2014

Oracle hierarchical queries

Discovered for myself Oracle hierarchical queries power.

Let’s assume you have a table with structure (basically storing graph representation in 1 table, parent is referencing to id in same table):

create table nodes(
    id number not null,
    parent number,
    type varchar2(10) not null);

table-hierarchical-queries

Let’s imagine we want to count how many children each parent in this structure has (any node can be both parent and child):


select parent, count(id)
from nodes
    start with id = 1
    connect by prior id = parent
group by parent;

Here you can use start with syntax to indicate id which you are asking to start search from.

In this case it will start with specific id = 1, that for each children matched by id = parent it will repeat same operation – looking for the children – therefore full tree will be walked through.

Connect by syntax allows to indicate condition which is used to find matched across whole hierarchy: on the left side of equation (id) you need to indicate parent reference. On the right side (parent) – you need to indicate child reference you are matching with parent.

You can play with SQL provided example here:

http://sqlfiddle.com/#!4/04b91/12