Home » SQL & PL/SQL » SQL & PL/SQL » How to flatten a hierarchy
How to flatten a hierarchy [message #197266] Tue, 10 October 2006 17:32 Go to next message
khanna111
Messages: 5
Registered: October 2006
Junior Member
Hi,

There is a self referenced table GROUP_GRAPH and it has two fields:

GROUP_GRAPH
-----------
group_id
member_group_id

Suppose the data is:
group_id | member_group_id
----------------------------
10 | 11
11 | 12


As you would have guessed by now, the group_id contains the groups that a group specified in member_group_id belongs to.

The requirement is to create a view that can hold the flattened list of this hierarchy. Although this can be achieved by the connect_by_root clause but earlier versions than 10G have to be supported too and this feature is available from 10G onwards.

Therefore the data to be retrieved by such a query would be:
group_id | member_group_id
----------------------------
10 | 11
10 | 12
11 | 12

Is there any SQL that can provide what is being asked here?

Thanks
Re: How to flatten a hierarchy [message #197285 is a reply to message #197266] Tue, 10 October 2006 21:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In 9i, you can use the SYS_CONNECT_BY_PATH() and SUBSTR() functions to get something similar.

For 8i and earlier, you would have to use PL/SQL or a bunch of self-outer-joins (yuck).

Ross Leishman
Re: How to flatten a hierarchy [message #197524 is a reply to message #197285] Wed, 11 October 2006 11:02 Go to previous messageGo to next message
khanna111
Messages: 5
Registered: October 2006
Junior Member
Hi Ross,

Thanks for the answer. If use the substr function, would that not be a sucker in terms of performance? What do you think about that?

Thanks
Re: How to flatten a hierarchy [message #197562 is a reply to message #197524] Wed, 11 October 2006 17:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What are your options other than SUBSTR().

Sure SUBSTR() is slower than selecting a raw unaltered column. How much slower? Benchmark it and find out.

Pick a big table and type:
SELECT MAX(some_varchar2_col), COUNT(*)
FROM   bigtab


Repeat it a couple of times to get any cacheing benefits.

Now try
SELECT MAX(SUBSTR(some_varchar2_col,1,3)), COUNT(*)
FROM   bigtab


Find the difference between the elapsed time of both queries and divide by the number of rows in the table.

I did this just now. Q1 took 38s, and Q2 took 40s. The table had about 7M rows. So thats a raw cost of 0.0000003 seconds per substring performed. I can live with that.

Ross Leishman
Re: How to flatten a hierarchy [message #197824 is a reply to message #197562] Thu, 12 October 2006 17:48 Go to previous message
khanna111
Messages: 5
Registered: October 2006
Junior Member
Thanks Ross.
Previous Topic: SQL AGGREGATE AND FORMAT QUESTION
Next Topic: query by categorized parameter
Goto Forum:
  


Current Time: Wed Dec 07 04:41:49 CST 2016

Total time taken to generate the page: 0.09869 seconds