select from collections performance problems

hello,

This is a newbie collection question. Has anyony any experience with TABLE(CAST( operators? I found them very ineffective when the size of collection is considerable. Perhaps I'm doing something wrong here. This is more or less what I'm trying to get:

SELECT CAST(MULTISET(SELECT column1, column2, column3

			FROM table1
			WHERE id IN 
				(SELECT ID 
					FROM TABLE(CAST(:collection1 AS COLLECTION_TYPE1))) 
			AS COLLECTION_TYPE2) INTO collection2 FROM DUAL;

table1 is 1M record table indexed on id. COLLECTION_TYPE1 and COLLECTION_TYPE2 are my own collection types. This query works quite fine for short collection1 (let's say up to 50 elements), for bigger ones it work really slowly.

Am I doing something wrong here? Can I improve my query somehow?

any tips&tricks appreciated
thanks
Grzegorz Received on Thu Jun 06 2002 - 20:27:14 CEST