2 Replies Latest reply: Oct 4, 2012 6:28 PM by rp0428 RSS

    Filter a collection?

    655717
      I was wondering if there was a more efficient way -- performance wise -- to filter a collection in Oracle 11.2g than simply using an IF block? For instance, if my collection has a field called id, would there be a more efficient way to retrieve all the records where the id field is equal to one than this:
      FOR i IN 1 .. ary.COUNT
      LOOP
          IF ary(i).id = 1 
          THEN
                  do something here
          END IF;
      END LOOP;
        • 1. Re: Filter a collection?
          Sven W.
          user652714 wrote:
          I was wondering if there was a more efficient way -- performance wise -- to filter a collection in Oracle 11.2g than simply using an IF block? For instance, if my collection has a field called id, would there be a more efficient way to retrieve all the records where the id field is equal to one than this:
          FOR i IN 1 .. ary.COUNT
          LOOP
          IF ary(i).id = 1 
          THEN
          do something here
          END IF;
          END LOOP;
          ID field sounds like it is unique. Therefore it would be very efficient if you just access this collection using this unquie (index) field.
          One option is to build you own index for the collection.
          /* first build the index */
          FOR i IN 1 .. ary.COUNT
            LOOP
                 arxIndex(ary(i).id) := i;
          end loop;
          
          /* then use the index */
               do something here with 
               ary(aryIndex(1)) 
          The index collection of cause has to be build one time.
          But then it can be reused over and over again.

          Of cause you see that this is just mimicking what normal SQL operations do out of the box.
          • 2. Re: Filter a collection?
            rp0428
            >
            I was wondering if there was a more efficient way -- performance wise -- to filter a collection in Oracle 11.2g than simply using an IF block? For instance, if my collection has a field called id, would there be a more efficient way to retrieve all the records where the id field is equal to one than this:
            >
            No - not if you just need to access that particular data one time only.

            And for multiple access (i.e. if you want to use the collection like a table) you should create a global temporary table and populate it instead of populating your collection. Then you can use SQL instead of PL/SQL and array access.

            If you absolutely must use PL/SQL then you can use a SQL type and query the collection as if it were a table.

            See my reply in this thread from today.
            Joining java array with SQL table in a stored procedure