Concatenating collections in PLSQL

Question

I need to collect a lot of ids from a couple of different tables into a variable of some sort to be passed to another function. Which tables to take the ids from is dynamic, depending on the param iVar below. The question is if there is no better way to do this as this approach will have to copy and re allocate the arrays multiple times. Would it be better to insert it all in a temp table? Would it be better to use dynamic sql. See the get_ids function below:

FUNCTION concat (
    iList1 IN ID_ARRAY,
iList2 IN ID_ARRAY
) 
RETURN ID_ARRAY IS
    lConcat ID_ARRAY;
BEGIN
    SELECT column_value BULK COLLECT INTO lConcat FROM (
        (SELECT column_value FROM TABLE(CAST( iList1 AS ID_ARRAY))) 
        UNION ALL 
        (SELECT column_value FROM TABLE(CAST( iList2 AS ID_ARRAY)))
    );
    RETURN lConcat;
END concat;

FUNCTION get_ids (
    iVar           IN NUMBER
) 
RETURN ID_ID_ARRAY IS
    lIds ID_ARRAY;
BEGIN
    lids := get_ids0();
    IF iVar = 1 THEN
        lIds := concat(lFilter, get_ids1());
    ELSE
        lIds := concat(lFilter, get_ids3());
        IF iVar = 4 THEN
            lIds := concat(lFilter, get_ids4());
        END IF;
    END IF;
    RETURN lIds;
END get_ids;

Answer 1

If you are using 10g or later you can make the CONCAT() function a bit more efficient by using the MULTISET UNION operator:

FUNCTION concat (
    iList1 IN ID_ARRAY,
      iList2 IN ID_ARRAY
) 
RETURN ID_ARRAY IS
    lConcat ID_ARRAY;
BEGIN
    lConcat := iList1 
               MULTISET UNION  
               iList2 A
    ;
    RETURN lConcat;
END concat;

You could make things more efficient by populating several different arrays and then calling MULTISET UNION once for all of them:

   lConcat := iList1 
               MULTISET UNION  
               iList2  
               MULTISET UNION  
               iList3
               MULTISET UNION  
               iList4;  

Using dynamic SQL - presumably to replace the various get_idsN() functions - might be an approach worth investigating, but probably won't give you much, if anything, in the way of improved performance.

Temporary tables are not a good idea, because they perform very badly compared to doing things in memory.

Answer 2

It turns out there is a much simpler way to concatenate:

iList1 MULTISET UNION ALL iList2

(Credit to https://forums.oracle.com/forums/thread.jspa?messageID=7420028. I had no idea this was possible until today.)