Let's start with a nested table
create type tt_int as table of integer;
There are two ways to find its distinct values. One is obvious
SELECT DISTINCT column_value
BULK COLLECT INTO v_result
FROM table(p_values);
And the other less obvious
v_result := p_values MULTISET UNION DISTINCT new tt_int();
At least on Oracle 12.1 it turns out that there is no clear winner between the two. Above 150-200 elements the SQL engine performs better (even considering consistent gets), but below that MULTISET UNION DISTINCT wins out.
c_empty_ints CONSTANT tt_int := NEW tt_int();
c_sql_hash_threshold CONSTANT PLS_INTEGER := 200;
FUNCTION distinct_values(p_values IN tt_int)
RETURN tt_int
IS
v_distinct tt_int;
BEGIN
IF p_values.COUNT < c_sql_hash_threshold
THEN
v_distinct := p_values MULTISET UNION DISTINCT c_empty_ints;
ELSE
SELECT DISTINCT COLUMN_VALUE
BULK COLLECT INTO v_distinct
FROM TABLE(p_values);
END IF;
RETURN v_distinct;
END distinct_values;
I'd be interested to hear if this holds with newer releases.
Cheers.