SQL Statement

Can anyone tell me why this is happening and what can be done to rectify this problem...

I've got the following SQL...

SELECT TABLE_A.COL_1, TABLE_A.COL_2, TABLE_A.COL_3 FROM DATABASE.TABLE_A
WHERE TABLE_A.COL_4 = 'VAR_1' AND

       TABLE_A.COL_1 IN
	    (SELECT /*+ INDEX(TABLE_B.INDEX_1) */ TABLE_B.COL_1
	     FROM DATABASE.TABLE_B
	     WHERE TABLE_B.COL_2 = 'VAR_2' AND
		   TABLE_B.COL_3 = 'VAR_3' AND
		   TABLE_B.COL_4 >= SYSDATE);

Both Table_A and Table_B are 100,000+ rows!

When I execute this, it works just fine up to a point! If the sub-select returns less then ~8000 rows, the primary select excutes very quickly. However, if the sub-select returns more than ~8000 rows, the primary select goes into a sweep. The DBA's don't know why this is happening! Is there something at the ORACLE side that needs to be set (eg. buffer sizes?, temp space?, indexes?). I've also noticed that if I execute the sub-select on its own and return less than ~8000 rows and then re-execute right after, the results come back instantly. However, if the sub-select returns more then ~8000 rows and I re-execute it, it takes the same amount of time as the original select (ie. the original results are not held in the buffer).

Any ideas???

Thanks!

Paul Ferrie
Advancement Services
The University of Western Ontario
DASPAF_at_UWOADMIN.UWO.CA Received on Tue Jan 28 1997 - 00:00:00 CST