The Oracle 10g database release has a new set
of Advanced Multiset support operators for SQL Server 2003, which
you can use with nested tables in an Oracle database. This new SQL
syntax provides powerful functionality that has been missing from
nested tables since they were introduced in Oracle 8i.
Unfortunately, the examples in the manual are complicated, making
it hard to see how these new functions work.

For a much simpler example, let’s use a nested
table of integers. When creating a table of nested tables, you must
specify the name of a table to create that will store the nested
table values.

create or replace type num_tab_typ as table of
number;
/
create table num_tab (num num_tab_typ)
nested table num store as num_tab_store;

I’ll populate the table with several lists of
numbers, integers from 1 to 5, odd numbers and even numbers.

insert into num_tab values
(num_tab_typ(1,2,3,4,5));
insert into num_tab values (num_tab_typ(1,3,5));
insert into num_tab values (num_tab_typ(2,4,6));
select num from num_tab;

NUM
————————–
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
NUM_TAB_TYP(2, 4, 6)

The multiset operator EXCEPT allows you to
return all the values from one nested table that aren’t found in a
second nested table in a query. For example the following query
expression returns a nested table of the original value, except for
1.

select num multiset except num_tab_typ(1) num from
num_tab;

NUM
————————–
NUM_TAB_TYP(2, 3, 4, 5)
NUM_TAB_TYP(3, 5)
NUM_TAB_TYP(2, 4, 6)

The multiset operator INTERSECT allows you to
return all the values which are common between two nested tables.
Using a nested table with just the value 1 will return an empty set
if 1 doesn’t exist, and a nested table with the value 1 if it does
exist.

select num multiset intersect num_tab_typ(1) num
from num_tab

NUM
————————–
NUM_TAB_TYP(1)
NUM_TAB_TYP(1)
NUM_TAB_TYP()

The multiset operator UNION allows you to
return all the values from both nested tables merged into a single
nested table.

select num multiset union num_tab_typ(1) num from
num_tab;

NUM
————————–
NUM_TAB_TYP(1, 2, 3, 4, 5, 1)
NUM_TAB_TYP(1, 3, 5, 1)
NUM_TAB_TYP(2, 4, 6, 1)

Notice that nested tables don’t have to contain
unique values. With the UNION operator above, there were two
instances where you ended up with duplicate values. If you want
distinct values in a nested table, you only need to add the keyword
DISTINCT to remove duplicate values.

select num multiset union distinct num_tab_typ(1)
num from num_tab;

NUM
————————–
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
NUM_TAB_TYP(2, 4, 6, 1)

In each of the nested table multiset operators,
the operator chooses one value from each set for inclusion or
exclusion. If there are duplicate values, only one will be removed.
For example, num_tab_typ(1,1,2,3) MULTISET EXCEPT
num_tab_typ(1,2,3) will result in num_tab_typ(1).

In Oracle 10g, you may now compare two nested
tables for equality. It doesn’t matter what order the elements are
in, as long as there are the same number and values.

select num from num_tab where num =
num_tab_typ(1,3,5);

NUM
————————–
NUM_TAB_TYP(1, 3, 5)

select num from num_tab where num = num_tab_typ(1,5,3);

NUM
————————–
NUM_TAB_TYP(1, 3, 5)

select num from num_tab where num = num_tab_typ(1,1,3,5);

no rows selected

You can now return the number of elements in a
nested table with the CARDINALITY function.

select cardinality(num) from num_tab;

CARDINALITY(NUM)
—————-
               5

               3

               3

The database considers a nested table with no
duplicate values a SET. In fact, there is a function SET that will
convert a nested table into a SET by removing duplicate values.
There is also a new SQL clause, IS A SET, which is true when the
values aren’t duplicated.

select num from num_tab where num is a set;

NUM
————————–
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
NUM_TAB_TYP(2, 4, 6)

select num from num_tab where num multiset union num_tab_typ(1) is
a set;

NUM
————————–
NUM_TAB_TYP(2, 4, 6)

Now you can test for a nested table having no
values with the new SQL clause IS EMPTY.

select num from num_tab where num multiset
intersect
num_tab_typ(1) is empty;

NUM
————————–
NUM_TAB_TYP(2, 4, 6)

There is a new SQL clause that allows you to
test a value for membership within a nested table.

select num from num_tab where 3 member of
num;

NUM
————————–
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)

select num from num_tab where 3 not member of num;

NUM
————————–
NUM_TAB_TYP(2, 4, 6)

You can also test if a nested table is a proper
subset of a multiset with SUBMULTISET.

select num from num_tab where num_tab_typ(1,3,5)
submultiset of num;

NUM
————————–
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)

Finally, there is an interesting function that
will create every possible subset of a particular multiset called
POWERMULTISET.

create or replace type num_tab_tab_typ as table of
num_tab_typ;
/
show errors;

select cast(powermultiset(num) as num_tab_tab_typ) from
num_tab
 where num = num_tab_typ(2,4,6);

CAST(POWERMULTISET(NUM)ASNUM_TAB_TAB_TYP)
————————–
NUM_TAB_TAB_TYP(NUM_TAB_TYP(2), NUM_TAB_TYP(4), NUM_TAB_TYP(2,
4),
 NUM_TAB_TYP(6), NUM_TAB_TYP(2, 6), NUM_TAB_TYP(4, 6),
NUM_TAB_TYP(2, 4, 6))

You can restrict the results of POWERMULTISET
to a certain number of elements with POWERMULTISET_BY_CARDINALITY.
For example, here’s the code you would use to return every possible
subset of the previous example that has two elements.

select cast(powermultiset_by_cardinality(num,2) as
num_tab_tab_typ) num
from num_tab
where num = num_tab_typ(2,4,6);

NUM
————————–
NUM_TAB_TAB_TYP(NUM_TAB_TYP(2, 4), NUM_TAB_TYP(2, 6),
NUM_TAB_TYP(4, 6))

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!