Re: Nested sort, trying again

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:

1. The table has to be to be joined via a full table scan to itself.
2. 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.
3. Path elements are collected into a nested table
4. 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 Mon Oct 03 2005 - 17:52:17 CDT