Re: Nested sort, trying again

vc wrote:
> Unfortunately, your example is broken because you rely on 'group by'
> supplying rows for concatenation in the correct order. It's not a
> correct assumption as the following experiment shows (the m.p. is
> reversed):
>
> --
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> CREATE or replace TYPE strings AS TABLE OF VARCHAR2(100);
> 2 /
>
> Type created.
>
> SQL>
> SQL>
> SQL> CREATE or replace Function CONCAT_LIST ( lst IN strings )
> 2 RETURN VARCHAR2 AS
> 3 ret varchar2(1000);
> 4 BEGIN
> 5 FOR j IN 1..lst.LAST LOOP
> 6 ret := ret || lst(j);
> 7 END LOOP;
> 8 RETURN ret;
> 9 END;
> 10 /
>
> Function created.
>
> SQL>
> SQL>
> SQL> create table input (
> 2 left integer,
> 3 right integer,
> 4 name varchar2(10)
> 5 );
>
> Table created.
>
> SQL>
> SQL>
> SQL> insert into input
> 2 select 1 left, 10 right, 'A' name from dual
> 3 union
> 4 select 2, 3, 'C' from dual
> 5 union
> 6 select 4, 5, 'B' from dual
> 7 ;
>
> 3 rows created.
>
> SQL>
> SQL>
> SQL> SELECT ii.left
> 2 ,CONCAT_LIST(CAST( COLLECT('.'||i.name) AS strings )) path
> 3 FROM input i, input ii
> 4 where ii.left between i.left and i.right
> 5 group by ii.left;
>
> LEFT PATH
>
> 4 .B.A
> 2 .C.A
> 1 .A
>
> SQL>
> --
>
>
> 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. Alternatively, one can use the "CONCAT_LIST( CAST(MULTISET(" syntax, which I assume takes into account the ordering inside the scalar subquery.

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. Received on Mon Oct 03 2005 - 23:44:11 CEST