Matthew Turland wrote on his blog, “[the nested set] model is simple and intuitive, but it has a drawback: to obtain any significant portion of the hierarchy stored in a table can take up to as many queries as there are levels of depth in the hierarchy.” I like the theory that Matthew talks about and the implementation for Oracle provided by developer.com got me thinking. I wasn’t so sure that some of the analytic functions in Oracle couldn’t make this happen faster/better/cheaper, so I set out to figure it out.

One of the primary concerns with the nested set model is the requirement that N number of rows would have to be updated with each new item. This is a deal-breaker in my job and wouldn’t fly, so I’ve added an additional column to assist in the solution that identifies the level at which the item occurs.

Given the following data set:

Here’s the query I came up with:

SELECT max(ltrim(sys_connect_by_path(site_area, ' > '),' > '))
FROM (
SELECT
site_area,
site_link,
row_number() over (partition by 1 order by site_level) level_index
FROM hierarchical_data
START WITH id=8
CONNECT BY PRIOR parent_id=id
)
START WITH level_index = 1
CONNECT BY level_index = prior level_index + 1;

And the resulting data set:

Let me know what you think… and be nice.