SQL - найти точную группу записей в большой таблице

Question

У меня есть следующие данные:

ID  --- GRP_ID  --- REC_VAL
1   --- 1       --- A
2   --- 2       --- A
3   --- 2       --- B
4   --- 3       --- A
5   --- 3       --- B
6   --- 3       --- C
7   --- 4       --- A
8   --- 4       --- B
9   --- 4       --- C
10  --- 5       --- A
11  --- 5       --- B
12  --- 5       --- E

Есть ли способ, как найти идентификатор группы записей, которые имеют одинаковые значения? (в этом случае только grp_id 3 и 4 имеют одинаковые значения)

Второй вопрос:

Есть ли эффективный способ найти точный grp_id, когда у меня был набор значений? Мое решение не очень быстрое, потому что таблица с группами имеет более 6 миллионов. записей:

-- Large table - up to 6m records
create table tmp_grp (id number, grp_id number, rec_val varchar2(10));
--
insert into tmp_grp(id, grp_id, rec_val) values (1, 1, 'A');
insert into tmp_grp(id, grp_id, rec_val) values (2, 2, 'A');
insert into tmp_grp(id, grp_id, rec_val) values (3, 2, 'B');
insert into tmp_grp(id, grp_id, rec_val) values (4, 3, 'A');
insert into tmp_grp(id, grp_id, rec_val) values (5, 3, 'B');
insert into tmp_grp(id, grp_id, rec_val) values (6, 3, 'C');
insert into tmp_grp(id, grp_id, rec_val) values (7, 4, 'A');
insert into tmp_grp(id, grp_id, rec_val) values (8, 4, 'B');
insert into tmp_grp(id, grp_id, rec_val) values (9, 4, 'C');
insert into tmp_grp(id, grp_id, rec_val) values (10, 5, 'A');
insert into tmp_grp(id, grp_id, rec_val) values (11, 5, 'B');
insert into tmp_grp(id, grp_id, rec_val) values (12, 5, 'E');
commit;
--
-- CTE representing record group for asking  
WITH datrec AS
 (SELECT 'A' rec FROM dual UNION ALL 
  SELECT 'B' rec FROM dual)
--
SELECT x.grp_id
  FROM (
  -- Count of joined records 
  SELECT COUNT(1) cnt, t.grp_id
          FROM tmp_grp t
          JOIN datrec d
            ON d.rec = t.rec_val
         GROUP BY t.grp_id
  -- 
  ) x
 WHERE 
 -- Count of all data records
 x.cnt = (SELECT COUNT(1) FROM datrec)
 -- Count of all group records
 AND x.cnt = (SELECT COUNT(1) FROM tmp_grp g WHERE x.grp_id = g.grp_id);
 --

Этот вопрос аналогичен поиску группы записей, которые соответствуют нескольким значениям, но в этом разделе рассматривается только точный набор значений (количество значений и значений в столбце rec datrec будет предоставлено другим запросом) и запросить группы возврата, содержащие этот набор. Мне нужно вернуть только точное совпадение.

ОБНОВЛЕНИЕ- добавлены данные в таблицу для лучшего уточнения

Также связано с Как сравнить группы кортежей в SQL

Answer 1

Вот способ избежать объединения базовой таблицы с самим собой. Это будет более эффективным, особенно если есть несколько (много?) Возможных значений rec_val для каждого grp_id, Это можно сделать еще быстрее, если grp_id уже существует где-то в ваших данных; Я создаю их на лету.

with gid ( grp_id ) as (
       select distinct grp_id from tmp_grp
     ),
     prep ( grp_id_1, grp_id_2, rec_val ) as (
       select t.grp_id, g.grp_id, t.rec_val
         from tmp_grp t join gid g on t.grp_id < g.grp_id
       union all
       select g.grp_id, t.grp_id, t.rec_val
         from gid g join tmp_grp t on g.grp_id < t.grp_id
     ),
     counts ( grp_id_1, grp_id_2, cnt ) as (
       select   grp_id_1, grp_id_2, count(*)
       from     prep
       group by grp_id_1, grp_id_2, rec_val
     )
select   grp_id_1, grp_id_2
from     counts
group by grp_id_1, grp_id_2
having min(cnt) = 2
;