From: kiurek@poczta.onet.pl (Grzegorz Mackiewicz)
Newsgroups: comp.databases.oracle
Subject: select from collections performance problems
Date: 6 Jun 2002 11:27:14 -0700
Organization: http://groups.google.com/
Lines: 24
Message-ID: <c47cd990.0206061027.7f6177f3@posting.google.com>
NNTP-Posting-Host: 213.134.142.50
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1023388034 25514 127.0.0.1 (6 Jun 2002 18:27:14 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 6 Jun 2002 18:27:14 GMT


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