This discussion is archived
6 Replies Latest reply: Nov 30, 2012 12:21 PM by BillyVerreynne RSS

Bulk collect

user10708429 Newbie
Currently Being Moderated
hi

oracle 10.2.0.1

Does bulk collect clear the elements it fetched previously into collection when running in a loop ?

Thanks
VK
  • 1. Re: Bulk collect
    Solomon Yakobson Guru
    Currently Being Moderated
    Yes. Collection is re-initialized.

    SY.

    Edited by: Solomon Yakobson on Nov 29, 2012 10:11 AM
  • 2. Re: Bulk collect
    Suman Rana Explorer
    Currently Being Moderated
    Yes.. bulk collect will always clear all the elements in collection, which were previously assigned into it and re-initialized the collection. if no elements were assigned into it and it was Un-initialized before using Bulk Collect, it will initialized the collection and assign the elements into the collection, starting with Subscript Index = 1. If no record is returned by the SQL query, where u r using BULK COLLECT, it will only initialize the collection and no element will be assigned into it.
  • 3. Re: Bulk collect
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user10708429 wrote:

    oracle 10.2.0.1
    Does bulk collect clear the elements it fetched previously into collection when running in a loop ?
    Yes, but it does NOT release and free memory allocated. If that collection was 1.5MB, and the new fetch reinitialises that as a 256KB collection, that does not mean the kernel is going to see 1+ MB of freed memory space.
  • 4. Re: Bulk collect
    chris227 Guru
    Currently Being Moderated
    Billy  Verreynne  wrote:
    user10708429 wrote:

    oracle 10.2.0.1
    Does bulk collect clear the elements it fetched previously into collection when running in a loop ?
    Yes, but it does NOT release and free memory allocated. If that collection was 1.5MB, and the new fetch reinitialises that as a 256KB collection, that does not mean the kernel is going to see 1+ MB of freed memory space.
    Is there something like garbage collection?
  • 5. Re: Bulk collect
    BluShadow Guru Moderator
    Currently Being Moderated
    chris227 wrote:
    Billy  Verreynne  wrote:
    user10708429 wrote:

    oracle 10.2.0.1
    Does bulk collect clear the elements it fetched previously into collection when running in a loop ?
    Yes, but it does NOT release and free memory allocated. If that collection was 1.5MB, and the new fetch reinitialises that as a 256KB collection, that does not mean the kernel is going to see 1+ MB of freed memory space.
    Is there something like garbage collection?
    Once the collection variable goes out of scope (i.e. the procedure ends) then memory will be marked for garbage collection.
  • 6. Re: Bulk collect
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    chris227 wrote:

    Is there something like garbage collection?
    Yep. But it is unlikely that the memory will be given back to the kernel as free memory - so do not bank on creating a large collection and chucking it away in "quick" fashion, so as not to consume a large chunk of server memory for an extended time. The only real guarantee that allocated memory will be freed is when that process (e.g. dedicated Oracle server process) terminates.

    And this is not really a PL/SQL engine issue - it would be the same for a C/C++ process, Pascal process, or whatever else. As the underlying issue is how the kernel deals with memory.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points