"vc" <boston103_at_hotmail.com> a écrit dans le message de news: 1160063872.972376.141210_at_h48g2000cwc.googlegroups.com...
Michel Cadot wrote:
> "Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 4524c3f1$0$13086$9b4e6d93_at_newsspool2.arcor-online.net...
> | Michel Cadot schrieb:
> | > "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159993002.453179.263340_at_e3g2000cwe.googlegroups.com...
> | >
> | > Mike C wrote:
> | >> Michel Cadot wrote:
> | >>> "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159989707.458848.89750_at_c28g2000cwb.googlegroups.com...
> | >>> I've already used the connect by to get the data in this format....
> | >>>
> | >>> -----------------------
> | >>>
> | >>> - Don't top post
> | >>> - So go on, you can easely get the sum over the hierarchy with a connect by.
> | >>>
> | >>> Regards
> | >>> Michel Cadot
> | >> This doesn't really need a CONNECT BY as it only needs to look at 1
> | >> level below. To get the sum for level 1, it only needs to look at
> | >> level 2, etc. If I could "easily get the sum" I would have done it and
> | >> wouldn't have posted to the group!
> | >
> | > I know I can write it something like this, but I was hoping to not have
> | > to repeat code and do it using one of the analytic fucntions if
> | > possible.
> | >
> | > SELECT NAME, level, decode(level,4,t1.frequency,t2.frequency) from
> | > table1 t1,
> | > (select parent_id, sum(frequency) FROM table1 group by parent_id) t2
> | > WHERE t1.id=t2.parent_id(+)
> | >
> | > --------------------------
> | >
> | > Analytic functions are useless here.
> | > From your exemple, you have to walk through the whole hierarchy
> | > and not just one level (else how do you 30 for id 1?).
> | > And connect by is the only way to go through a hierarchy.
> | > I don't think you can get something with your query.
> | >
> | > Regards
> | > Michel Cadot
> | >
> | >
> | The straightforward approach would be probably like
> |
> | SELECT Empno,
> | Ename,
> | Sal + Nvl((SELECT SUM(Sal)
> | FROM Scott.Emp i
> | CONNECT BY PRIOR Empno = Mgr
> | START WITH o.Empno = i.Mgr),
> | 0) Total_SubTree_Sal
> | FROM Scott.Emp o
> |
> | Not sure, about the performance with correlated hierarchical subquery,
> | whereas with not too big volumes of data ( and not too deep hierarchy)
> | should be ok.
> |
> | It might be interesting, if this result can be achieved without
> | correlated subquery however...
> |
> | Best regards
> |
> | Maxim
>
> I don't think so.
select
a.id,
a.name,
parent,
sum(b.frequency)
from
(select id, name, parent, sys_connect_by_path(id, '/') path
from t1 connect by prior id = parent start with parent is null
) a,
(select frequency, sys_connect_by_path(id, '/') path
from t1 connect by prior id = parent start with parent is null
) b
where instr(b.path, a.path) = 1
group by a.id, a.name, a.parent
/
When I said I don't think I meant I don't think you can build
an *efficient* query without a correlated subquery.
Yes, you can write many awful queries like yours building
twice the hierarchy with nested loops between the two.
A little less worse version is:
with
data as (
select /*+ materialized */
id, name, parent, frequency,
sys_connect_by_path (id, '/') id_path
from t
connect by prior id = parent
start with parent is null
)
select a.id, a.name, a.parent, sum(b.frequency) frequency
from data a, data b
where instr(b.id_path,a.id_path) > 0
group by a.id, a.name, a.parent
order by a.id
/
which build a temporary table with the result of the connect by
but still leads to nested loops.
My first query is built around an hash join between the original
table and the full hierarchy generated by the connect by.
Regards
Michel Cadot
Received on Thu Oct 05 2006 - 12:19:21 CDT
