Querying on hierarchical tables [message #274081] |
Sat, 13 October 2007 09:32 |
moonwalker
Messages: 2 Registered: October 2007 Location: India
|
Junior Member |
|
|
Hi,
I have a 3-level hierarchical table having three scopes of records, say weekly, daily and day-detail. For a certain requirement, I have to pull out records of all scope in a hierarchical manner. I have three options, can somebody help me out with your thoughts on this ?? I work on 10g.
1. Write down a hierarchical query using the start with - connect by clause and let SQL build up the hierarchy and pull out the data.
2. Write down a query with self joins on the same table -- meaning, in the from clause, write down the table thrice, with aliases as weekly, daily and detail and manage as three separate tables, with where clauses for each alias to pull out only the corresponding table.
3. Use a GTT, which stores all the records -- first pull out the weekly scope guys, use this for the daily scope and use that for the detail scope. On comparison, this would require 3 sqls in place of one.
Why I thought about this option is because my table is quite big; 0.1 million records are the least I can expect and might grow up to 10 million quite frequently.
Each time I go thru the process, I would require to pull out around 100 weeks data -- essentially meaning 100 weekly records, 7 daily records for each week and on an average 1.5 details per day. That would mean under 5k records per process, and to build the hierarchy over 5k (with the GTT) using start with, connect by can be much easier.
I ruled out plsql tables for this requirement because there is no indexing available for them. While the tables can have indexes, if I dump the table data into plsql tables, I would have to walk thru the tables with .NEXT or .PREV or loop thru to process the data.
Can somebody share some views on this ??
Thanks,
Ashok.
|
|
|
Re: Querying on hierarchical tables [message #274082 is a reply to message #274081] |
Sat, 13 October 2007 09:38 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Table does not conform to Third Normal Form & now the application pays the price for the suboptimal architecture.
The scalable solution is to break the table into three (using Materialized View the two aggregations).
Or alternatively create two view on top of the pure daily table for the two aggregations.
IMO, there is NO need for any "hierarchical" table.
[Updated on: Sat, 13 October 2007 09:44] by Moderator Report message to a moderator
|
|
|
Re: Querying on hierarchical tables [message #274085 is a reply to message #274082] |
Sat, 13 October 2007 10:51 |
moonwalker
Messages: 2 Registered: October 2007 Location: India
|
Junior Member |
|
|
Thanks for your time on this !
I do understand that the design is not good enough, but what I am gonna do is as small as a report running on the table data.
Did you mean I have to break the table down and reengineer the whole application for best performance ?? Well, I wouldnt get a green signal for that to do such a small requirement.
Or you meant creation of views on top of the existing table ??
"The scalable solution is to break the table into three (using Materialized View the two aggregations). " If i use a materialized view, the users wouldnt always get the right data until a refresh is done, right ?? I am afraid I cant go that way, because this is one table which has a lot of on and off transactions every minute.
The second solution, to create views on top of the daily table, I dint understand that. Pls can you explain that ?
The reason why there is a hiearchical structure, I believe is that since it is storing time data, its actually not just a collection of days. If its a day level record, there are attributes for that, details have others for them, and weekly scope have data specific for them. Which is of the same nature, but definitely undispensible. And the top level scope, weekly as I mentioned is not a fixed scope -- it can be weekly, monthly or semimonthly or any number of days together. I hope you understood. Why I had wanted a hierarchical structure is cos I want my report to show the data like below.
Week starting
Day 1
Detail 1
Detail 2
Day 2
Detail 1
.
.
.
After I mention the scope, there are some attribute information that follows which make sense only for the specific scope, so either they have to be in separate tables or in a hierarchical tables.
Thanks a lot for your time.
|
|
|
|
Re: Querying on hierarchical tables [message #274121 is a reply to message #274088] |
Sun, 14 October 2007 02:10 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
The best solution will probably depend on the table structure. Why don't you show us what the table looks like and your preferred output?
I'm not sure any of these is a good solution. It should be possible to pick up all the rows you need with a single pass, and then order them cleverly with a DECODE() function to get the detail, daily, and weekly rows in the correct order.
Ross Leishman
|
|
|