"Ying Lin" <ylin_at_bsoft.com> wrote in message news:<VgYva.820094$3D1.464313_at_sccrnsc01>...
> I am doing some performance tuning for sys_connect_by_path function by using
> inline view to loop through all the roots
>
> the following create table statement took around 20 mins to generate 50000
> rows, but if I did open cursor loop from the root list(rootlist table), it's
> about 5 mins.
>
> Anyone have any idea?
>
> The query creating a table like this
>
> sup_mol_name, path
>
> -----------------------------------------------------------
>
> aaa 1-2-3-4-5
>
> bbb 11-12-13-14
>
> ccc 21-22
>
> .....
>
> ---------
>
> create table treepath
>
> as
>
> SELECT sup_mol_name,
>
> SUBSTR(sys_connect_by_path(id, '-'), 2) path,
>
> FROM t_id_hit hit
>
> WHERE hit.child_id IS NULL
>
> START WITH hit.id in (select root_id from rootlist )
>
> CONNECT BY PRIOR hit.child_id = hit.id
Can you clarify what takes 5 minutes?
Received on Tue May 13 2003 - 09:27:31 CDT
