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: