Data Management

Understand Oracle 10g's new multiset operators

Scott Stephens discusses Oracle 10g's new set of Advanced Multiset support operators for SQL Server 2003, which you can use with nested tables in an Oracle database.

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!

0 comments