Oracle POWERMULTISET Function

Which pairs of airports could have a direct route? Which combinations of three products could form a bundle? Such questions need every subset of a list. POWERMULTISET returns all non-empty subsets of a nested table, its power set, and POWERMULTISET_BY_CARDINALITY returns only the subsets of a given size.

Code for This Guide

The main example is in the examples/misc-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

powermultiset(nested_table)
powermultiset_by_cardinality(nested_table, cardinality)

The result is a nested table of nested tables, so it needs a type for each level, and it is read with TABLE().

Setup:

create or replace type t_codes as table of varchar2(10);
create or replace type t_code_sets as table of t_codes;

All Subsets and Subsets of One Size

The first query returns every non-empty subset of three airports; the second only the pairs.

Example:

select * from table(powermultiset(t_codes('LHR', 'SIN', 'SYD')));

select * from table(powermultiset_by_cardinality(t_codes('LHR', 'SIN', 'SYD'), 2));

Output:

COLUMN_VALUE
__________________
[LHR]
[SIN]
[LHR, SIN]
[SYD]
[LHR, SYD]
[SIN, SYD]
[LHR, SIN, SYD]

7 rows selected.

COLUMN_VALUE
_______________
[LHR, SIN]
[LHR, SYD]
[SIN, SYD]

Three elements give seven non-empty subsets, 2 to the power 3 minus the empty set. The three pairs are all the direct routes a network of three cities could have.

Count Subsets by Size

Each subset is itself a collection, so CARDINALITY measures it, and ordinary SQL groups the result.

Example:

-- how many subsets of each size four airports have
select cardinality(column_value) as subset_size, count(*) as subsets
from   table(powermultiset(t_codes('DXB', 'LHR', 'SIN', 'SYD')))
group  by cardinality(column_value)
order  by subset_size;

Output:

   SUBSET_SIZE    SUBSETS
______________ __________
             1          4
             2          6
             3          4
             4          1

Four airports have 4 single subsets, 6 pairs, 4 triples, and 1 set of all four: 15 in total.

Things to Know

• The number of subsets doubles with each element: 20 elements have more than a million subsets. Keep the input small, or use POWERMULTISET_BY_CARDINALITY to limit the size.
• For pairs only, a self-join with a.code < b.code is a simpler alternative that needs no types.
• Drop the types afterward with drop type t_code_sets and drop type t_codes.

Related Guides

• Oracle CARDINALITY Function
• Oracle COLLECT Function

Conclusion

POWERMULTISET returns every non-empty subset of a nested table, and POWERMULTISET_BY_CARDINALITY the subsets of one size. Define a collection type and a table of it for the result, read it with TABLE(), and keep the input small, since the number of subsets doubles with each element.