Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> sys_connect_by_path

sys_connect_by_path

From: Ying Lin <ylin_at_bsoft.com>
Date: Tue, 13 May 2003 01:56:05 GMT
Message-ID: <VgYva.820094$3D1.464313@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 Received on Mon May 12 2003 - 20:56:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US