Skip to Main Content
  • Questions

  • Does SELECT ... BULK COLLECT overload PGA?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sundar.

Asked: March 20, 2007 - 3:07 pm UTC

Last updated: March 26, 2007 - 7:08 am UTC

Version: 9.2.0.7

Viewed 1000+ times

You Asked

Hi Tom,
I read from your other article that when using FETCH .. BULK collect, without using LIMIT, there is a chance of blowing off the PGA. What about using SELECT <column_list> BULK COLLECT INTO <variable_list>. There is no limit clause for this, so does Oracle take care of fetching it in certain limits, or is there a chance of overloading the PGA in this case too?

Thanks,
Sundar

and Tom said...

unless you use LIMIT - then Oracle will fetch ALL rows from the defining query into an in memory array.

If you have insufficient memory to do that, you will get an ora-4030.


So, use select ... bulk collect into ... from ..... ONLY when you are sure - really really sure - that the result set will be of a reasonable size.

Any chance that it is not, use an explicit cursor and fetch with the LIMIT clause.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 23, 2007 - 2:32 pm UTC

How to be really sure the resultset will fit into the PGA?

If I know the number of rows, and from the query the size of the column, could you demonstrate with a simple example (say a bulk load from T, perhaps) how to check we won't hit Ora-4030?

Thanks
Tom Kyte
March 26, 2007 - 7:08 am UTC

you need no example, it is just a simple memory measurement here. If you have N rows and width is M, it'll be at least N*M in size - only bigger because it is a data structure in PLSQL (they have their bits and bytes they'll add).

Just be reasonable and use between 100 and 500 rows at a time. You do not want an entire result set in memory, you want a slice of it, work on it, get rid of it, get next slice of it.

alternative method

Connor, April 05, 2007 - 1:59 am UTC

or bulk collect into a varray. at least that way, when you grow to above what you were expecting, you'll get an error, not exhaust of all your memory
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.