Re: Nested sort, trying again
Date: 3 Oct 2005 15:52:17 -0700
Message-ID: <1128379937.485666.199730_at_z14g2000cwz.googlegroups.com>
Vadim Tropashko wrote:
> vc wrote:
....
> > Additionaly, if you have a parallel query, rows can arrive intermixed
> > from several parallel execution threads producing a meaningless
> > concatenation instead of the expected m.p.
>
> True, but easily fixable. The chain of ancestors is naturally ordered
> by the "left" column. Therefore, enhance the collection to become
> indexed, and amend the concatenation method with the sort.
That's correct, but doing so makes the performance which is already not stellar even worse:
- The table has to be to be joined via a full table scan to itself.
- Group by has to go through the result set from step 1 whose cardinality is the original N rows times the average number of ancestors.
- Path elements are collected into a nested table
- The nested table has to be sorted N times.
Why bother and not just use a 'connect by' or maintain an m.p. encoding from the very beginning ?
> Alternatively, one can use the "CONCAT_LIST( CAST(MULTISET(" syntax,
> which I assume takes into account the ordering inside the scalar
> subquery.
No, you do not have any control over the order in which rows are processed by a 'group by' unless you resort to using a user-defined aggregate and sort the stuff inside it, but then you do not need collect (which is faster than a u.d.a).
>
> Either way, there is little sence elaborating this idea in oracle, as
> the OP is evidently using some other RDBMS. If it's sybase anywhere, or
> mysql, then ordering should be already taken care of in the syntax.
Could you elaborate on those two ? Received on Tue Oct 04 2005 - 00:52:17 CEST