Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> sys_connect_by_path
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