hierarchies

Working with hierarchies in Oracle

Hi all! One of the most used database concept is  hierarchies. Why? Basically, any relation that can be categorized as a “Father” – “Son” relationship can take advantage of this concept. Lets us imagine you want to represent the relationship between an employer and an employee. You could probably create two tables like the ones below :

This is the typical no-brainer solution. But as you have noticed, both employer and employee tables share almost everything, so why bother creating two tables when we could represent the same relationship with only one table ? This is when the hierarchies concept comes to our help. If we can imagine that an employer is just an employee of himself, we could also represent the structure above as :

Here we have just defined the employer_id as a column with a foreign key to employee_id. Now if we populate this table, say with 2 employers and 2 employees we could issue a:

select * from employee order by 1

and see a possible result like the one below :

However this basic listing won’t help us much when we have thousands of records in this table. Finding the basic relation parent-child is still easy this way. But what if…we had a multi-hierarchies relation? Imagine that our “Larry Elison” here is the employer of “Richard Branson” who in turn is the boss of “Mary” and “John”…. How could we select all the employers and all the employees ? First lets change the table above to:

One of the ways to to show the hierarchies relationships is to use the ORACLE connect by clause in the following way :

select level,e.* from employee e start with e.employer_id is null
connect by prior e.employee_id = e.employer_id;

This would return :

I’ll now explain the SQL query.The level is an Oracle identifier that can be used with the connect by clause, and shows the number of levels between and employee and an employeer. The start with clause identifies the starting row condition. On my example, “Larry Ellison” having a null employeer_id will be the first.The connect by prior identifies the relationship between parent and row childs.

Now that we have put in evidence parent and row childs, how could we order the row childs by first name ? Oracle comes to our rescue again with the order by siblings clause. To put this into evidence I’ve added another employee named ‘Anna Johansson’ .Changing our query to

select level,e.*
from employee e
start with e.employer_id is null
connect by prior e.employee_id = e.employer_id
ORDER SIBLINGS BY first_name;

would return

What if I wanted to show the in the same line as the employee, the employee’s boss name ? Then I could use the:

 select level,
 CONNECT_BY_ROOT first_name "EMPLOYER NAME",
 e.employer_id,
 e.employee_id,
 e.first_name,
 e.last_name,
 e.email
 from employee e
 where LEVEL > 1
 connect by prior e.employee_id = e.employer_id
 ORDER SIBLINGS BY first_name;
 

This would return :

Really neat uh? 🙂  Just to finish this long post, we will imagine one last scenario. How to show the path  that connects the employer to the employee ? We can use the sys_connect_by_path clause, that will return the path from the root to the node. To use it,  change our query above to

 select level,
 CONNECT_BY_ROOT first_name "EMPLOYER NAME",
 SYS_CONNECT_BY_PATH(first_name, '/'),
 e.employer_id,
 e.employee_id,
 e.first_name,
 e.last_name,
 e.email
 from employee e
 where LEVEL > 1
 connect by prior e.employee_id = e.employer_id
 ORDER SIBLINGS BY first_name;
 

The result would be :

And that will be all for now ! If you would like to continue exploring hierarchies, I recommend this reading .Also, if you’re on the business intelligence field, you may also like the official Oracle definitions for hierarchies here. This last reading will introduce you to the concepts behind item hierarchies and date hierarchies that I won’t cover  for now.

Cheers,
Carlos