How can I write a procedure to find a set of rows like this?

Question

Sorry about the rubbish question title. I have a table SET_DEFINITIONS like this:

SETKEY      NOT NULL    NUMBER(38)
SETENTRY    NOT NULL    NUMBER(38)

where the idea is that the rows define sets of numbers. For example the table could contain rows:

1 2
1 4
2 1
2 2

which would mean set 1 is {2,4} and set 2 is {1,2}. I want to write a function

function selectOrInsertSet(table of number(38) numbers) return number(38)

which will return the key of a set with the same members as the passed in table (or create such a set if it doesn't exist). What's a good way to do this in PL/SQL?

EDIT: the solution I'm currently working on goes like this (and I'm not sure it'll work):

1. select all keys that have the first element into some collection c
2. refine the collection c by intersecting with successive sets of keys that contain the other elements

Answer 1

A quick way to find the intersection may be to create a global temporary table and populate it with the passed in table of numbers. You could then join this table with SET_DEFINITIONS to find all possible matches. You would need to do a check on the total number in each matched set to eliminate supersets.

Create some base tables...

create table set_definitions (
    setkey number,
    setentry number,
    constraint pk_set_definitions primary key (setkey, setentry)
    );

insert into set_definitions values (1,2);
insert into set_definitions values (1,4);
insert into set_definitions values (2,1);
insert into set_definitions values (2,2);
insert into set_definitions values (3,1);
insert into set_definitions values (3,2);
insert into set_definitions values (3,3);

Create a global temporary table to hold the passed on values:

create global temporary table tmp_setentry (
    setentry number, 
    constraint pk_tmp_setentry primary key (setentry));

insert into tmp_setentry values (1);
insert into tmp_setentry values (2);

Join with set_definitions to find the matching set(s):

select
    setkey
from
    (
    select
        setkey,
        count(*) num_matches,
        (select count(*) from set_definitions where setkey = s.setkey)
            num_set_entries,
        (select count(*) from tmp_setentry) num_entries
    from
        set_definitions s
            inner join tmp_setentry t on t.setentry = s.setentry
    group by
        setkey
    )
where
    num_matches = num_entries
and num_set_entries = num_entries

--> 2 (3 is dropped as a superset)

Hope this helps.

Answer 2

You can use a full outer join between each set and the collection of numbers to see if they are the same. This function does that:

function selectOrInsertSet(numbers number_tt) return number
is
  l_diff number;
  l_retval number;
begin
  for r in (select distinct setkey from set_definitions)
  loop
     with d as (select column_value from table(numbers)),
          s as (select setentry from set_definitions where setkey=r.setkey)
     select count(*)
     into   l_diff
     from   s
     full outer join d on d.column_value = s.setentry
     where s.setentry is null or d.column_value is null;

     if l_diff = 0 then
        l_retval := r.setkey;
        exit;
     end if;
  end loop;

  return l_retval;
end;

This returns the setkey if found, else null.

I haven't implemented the part about creating a new set if none is found, but that should be easy enough. I don't personally like functions that have side effects (in this case, inserting rows into a table).

Answer 3

Oracle 11g introduced LISTAGG function that ciould be used for what you need. Take example below as idea, as I'm not really familiar with oracle, but it should work with probably some minor corrections):

Create table set_definitions (setkey int, setentry int);
Create table searchFor (setentry int);

insert into set_definitions values (1,4);
insert into set_definitions values (2,1);
insert into set_definitions values (2,2);
insert into set_definitions values (3,1);
insert into set_definitions values (3,2);
insert into set_definitions values (3,3);

Insert Into searchFor Values (1);
Insert into searchFor Values (2);

With Prepare as 
(
Select setkey, LISTAGG(setentry, ',') WITHIN GROUP (ORDER BY setentry) as EntryList
  From set_definitions       
 Group by setkey
 Having Count(*)=(Select Count(*) From searchFor) -- Just to eliminate obvious ones 
)
Select setkey
  from Prepare 
 Where EntryList = (Select LISTAGG(setentry, ',')  WITHIN GROUP (ORDER BY setentry) From searchFor);