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);
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