6 Replies Latest reply: Nov 30, 2012 2:21 PM by Billy~Verreynne RSS

    Bulk collect

    user10708429
      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
          Yes. Collection is re-initialized.

          SY.

          Edited by: Solomon Yakobson on Nov 29, 2012 10:11 AM
          • 2. Re: Bulk collect
            Suman Rana
            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
              Billy~Verreynne
              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
                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
                  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
                    Billy~Verreynne
                    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.