Pooling Collections in PLSQL

Question

I need to collect many identifiers from several different tables into a variable of some type, which will be passed to another function. Which tables for entering identifiers are dynamic, depending on the iVar parameter below. The question is that there is no better way to do this, since this approach will have to be copied and reallocated arrays several times. Would it be better to insert all this into a temporary table? It would 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 use 10g or later, you can make the CONCAT () function more efficient with the MULTISET UNION statement:

 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 can make things more efficient by filling in 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 - may be suitable for learning, but probably will not give you much, it will improve performance.

Temporary tables are not a good idea, because they work very poorly compared to what happens in memory.

Answer 2

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

 iList1 MULTISET UNION ALL iList2 

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