Slow query

Hi,
here is my problem.
I have two tables "samplelist" and "materiallist" with the following structures:
samplelist
(snum NUMBER(10),

mat NUMBER(10),
PRIMARY KEY (snum));

materiallist (hierarchical list)
(mnum NUMBER(10),

parent NUMBER(10),
name VARCHAR2(80),
PRIMARY KEY (snum))

samplelist contains about 2000 records, materiallist contains about 400 records.
Now, this query takes about 50 seconds

SELECT COUNT(*) FROM samplelist
WHERE mat IN (SELECT mnum FROM materiallist START WITH mnum IN (4) CONNECT BY PRIOR mnum=parent)
 AND snum<=2949

and this query (without condition snum<=2949) takes only abut 3 seconds. Why is there so big diference? (CONNECT BY SELECT returns about 150 records)

SELECT COUNT(*) FROM samplelist
WHERE mat IN (SELECT mnum FROM materiallist START WITH mnum IN (4) CONNECT BY PRIOR mnum=parent)

Could anyone explain it?

Thank You

Michal Motalik
motalik_at_zl.inext.cz Received on Fri Jun 25 1999 - 01:27:35 CDT