My World A Blog On All The Things That Happen In My Life

Guardian of the roadSpinach Alfredo pizza with bacon,  cheese and jalapeños. Lunch by @joshenoy@joshenoy sitting besides me on the balconyFor this Friday evening.  Beer + pizzaChuudi - 3 flowers and Durva grass tied together with a string. @.@joshenoy crafting a bunch of these. #nofilterChudi pooja at homeIMG_20140616_162916IMG_20140613_220028
Status » Grid 2 gameplay video - Faceoff run with the Nissan Fairlady Z: http://t.co/IiAPdIA9sS via @YouTube

The Power and Simplicity of Oracle Hierarchical Queries
about Oracle & PL/SQL Stuff on 19/01/2009 - like it?

They say “Necessity is the Power of Invention” – a quote which I fully understood the impact today. I’ve been trying to understand Oracle’s Hierarchical Queries for a while now, but never fully understood the situation under which or when it would be used. Yesterday I got a reason to use it. Let me begin.

menuThe above picure shows the menu structure of the our application. Now the implementer wanted the menu structure, of each and _every_ module, in a spreadsheet so that he could forward it to the client and get the user authorization thing going(ie, who’s got authority to view/edit etc). Now under each module we have several menus and submenus, and manually entering these would be like ultimate FAIL (later ananlysis showed on an average, each module had about 80 entries). So I was figuring out how to extract this. Now I knew that all these entries were there in the database I was struggling with the query to extract it.

So the first query I came up was this:


SELECT MENU_SCR_NAME
FROM MENU_MENUS
WHERE MENU_PARENT_ID = '101'

This is what the query results were – a simple output.

Query results

Now I knew I’d be going nowhere with this simple query, and I knew normal joins won’t work, so I started looking at Hierarchical queries. Hierarchical queries(going to refer as h-queries) basically allow you to build queries, based on well, hierarchies. Parent – Child Relationships. Tree-Leaf style. Read a bit on h-queries and reconstructed the SQL query to the one below.

SELECT MENU_SCR_NAME
FROM MENU_MENUS
CONNECT BY PRIOR MENU_ID = MENU_PARENT_ID
START WITH MENU_ID = '101';

The result of the query is shown below:

query2

While the result of the query looks the same as the first, I knew that it was returning the results in a hierarchy,just check the first image. The keyword for this query is “Connect by” and “Prior” which transforms the query into a h-query. The “connect by” and “prior” gives the conditions for hierarachy in the query, with the column next to “prior” being the child column and the one next to equality being the parent column. The “start with” keyword tells Oracle which is the root record.

Now make things a little complicated, the database contains more records than what’s necessary, and I had to filter them based on user authorization conditions as well. So I added a subquery to filter out those records which belong to the superuser.

So the query now became a little bit more (unnecessarily) complicated:

SELECT Lpad(menu_scr_name,Length(menu_scr_name) + LEVEL * 4 - 4,'-')
FROM menu_menus
WHERE menu_id IN (SELECT um_menu_id
FROM menu_user_menus
WHERE um_group_id = 'USGRP'
AND um_menu_id IN (SELECT menu_id
FROM menu_menus
CONNECT BY PRIOR menu_id = menu_parent_id
START WITH menu_id = '101'))
CONNECT BY PRIOR menu_id = menu_parent_id
ORDER BY menu_id

The Lpad function is an Oracle PL/SQL function which adds padding of a specified character to the left. The results were not exactly what I was looking for.

query3

A bit of further reading and I came to know that order by destroys the hierarchy, and “order siblings” by is what is supported to be used.

So Modified the query to:
SELECT Lpad(menu_scr_name,Length(menu_scr_name) + LEVEL * 4 - 4,'-')
FROM menu_menus
WHERE menu_id IN (SELECT um_menu_id
FROM menu_user_menus
WHERE um_group_id = 'USGRP'
AND um_menu_id IN (SELECT menu_id
FROM menu_menus
CONNECT BY PRIOR menu_id = menu_parent_id
START WITH menu_id = '101'))
CONNECT BY PRIOR menu_id = menu_parent_id
ORDER SIBLINGS BY menu_id

And it all came into place. Almost.

Have a look:

query4

Unfortunately, it wasn’t quite right, as the query was going into an almost infinite loop, as the child record itself became the root record.

I had a look at the query again, and realized that my query itself was wrong, as corrected it to

SELECT Lpad(menu_scr_name,Length(menu_scr_name) + LEVEL * 4 - 4,'-') "Menu"
FROM menu_menus
WHERE menu_id IN (SELECT um_menu_id
FROM menu_user_menus
WHERE um_group_id = 'USGRP')
CONNECT BY NOCYCLE PRIOR menu_id = menu_parent_id
START WITH menu_id = '101'
ORDER SIBLINGS BY menu_seq_no;

So in essence, I got rid of the unnecessary inner sub-query and voila!

query5

Tags: , , , ,


2 comments - really? way cool!

Crap, all formatting went down the drain :|

Written by Sathya on 19/01/2009

"The Power and Simplicity of Oracle Hierarchical Queries » My World" – http://bit.ly/9AEjof via @Apture

Written by Mohd a on 23/06/2010

Leave some comment love