I think one of the main concerns is not the plsql
table in itself but the fact that its going to use a
hell of a lot of pga...I'd be more concerned about how
well oracle handles that, in particular, the
'randomness' factor ie if lots of pga already in use
elsewhere then you could get process memory errors
etc.
You could always add a LIMIT clause to process the
rows in batches of (say) 1000
hth
connor
- "Gorbounov,Vadim" <vadim.gorbounov_at_liberate.com>
wrote: > Thanks, Stephane,
>
> I don't have a good filling about having plsql table
> of that size either,
> although Oracle claims it must work up to 2^32.
>
> I was looking for some practical cases against plsql
> tables larger than X
> number of rows.
> This failure sounded a good one, but it happens
> occasionally, in most cases
> everything works fine. Yes, it works. Well, almost.
>
> I'll try to approach this from efficiency side now,
> at least in our case
> plsql table of 200000+ rows could not outperform
> temp table or plain SQL.
>
> Have a good day
> Vadim
>
>
>
> -----Original Message-----
> Sent: Tuesday, May 20, 2003 3:42 PM
> To: Multiple recipients of list ORACLE-L
>
>
> "Gorbounov,Vadim" wrote:
> >
> > Hi all,
> >
> > Is there any "best practice" regarding practical
> limits on PL/SQL tables?
> > We've got an issue here which may potentially
> invalidate data, no error
> > messages.
> >
> > Here is the case:
> >
> > Oracle9i Enterprise Edition Release 9.0.1.4.0,
> Solaris.
> >
> > DECLARE
> > TYPE t_subsvcparmid IS TABLE OF
> sub_svc_parm.sub_svc_parm_id%TYPE;
> > esubsvcparmid t_subsvcparmid;
> > BEGIN
> > SELECT /*+ index(sub_svc_parm,
> sub_svc_parm_ix2) */
> > sub_svc_parm_id
> > BULK COLLECT INTO esubsvcparmid
> > FROM sub_svc_parm
> > WHERE parm_id = 10;
> >
> > DBMS_OUTPUT.PUT_LINE(esubsvcparmid.LAST);
> > ..................................
> >
> > The select is expected to return close to 200K
> records, and usually it
> does,
> > but sometimes number of records is restricted to
> 65535 = 2^16-1, it can be
> > seen both from DBMS_OUTPUT and 10046 trace:
> >
> > FETCH
> >
>
#2:c=4370000,e=33876545,p=14895,cr=66960,cu=0,mis=0,r=65535,dep=1,og=4,tim=1
> > 052942377932150
> >
> > ^^^^^^^
> > Did anybody see such weird behavior?
> >
> > TIA
> >
> > Vadim Gorbounov
> >
> > Liberate Tech.
> >
>
> Vadim,
>
> I have never heard of this, but it only half
> surprises me - what
> surprises me most is that you have seen it work.
> It's a well known fact
> with Oracle that array fetches, probably because
> indices are unsigned
> short integers, have such a limit. PL/SQL tables are
> just arrays in
> disguise.
> I must say that I wouldn't think of using them for
> such a big number of
> rows. I am far from being a proponent of temporary
> tables, but that may
> be the case for using them.
>
> --
> Regards,
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Gorbounov,Vadim
> INET: vadim.gorbounov_at_liberate.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
It's Samaritans' Week. Help Samaritans help others.
Call 08709 000032 to give or donate online now at
http://www.samaritans.org/support/donations.shtm
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue May 20 2003 - 22:06:37 CDT
