两表关联时的子集判断问题

Question

如：
表1：test1
NAME            ID        
----------- ---------        
a                   1        
a                   2        
a                   3        
a                   4        表2：test2        ID  VALUE
---------- ----------
         1 good
         2 good
         1 bad
         2 bad
         3 bad
         4 bad
         5 bad
         3 nothingtest1和test2关联后的结果：
NAME        VALUE
---------- ----------
a                good也就是说test2中的id必须是test1的id的子集，两表才关联成功。其中test1的数据量为2亿条记录左右。test2的数据量在10万条记录左右。
各位能否提供一个高效的方法完成此功能。

Answer 1

select a.name,b.value
from test1 a,test2 b
where a.id(+)=b.id
group by a.name,b.value
having count(decode(a.id,null,1))=0

Answer 2

其中test1的数据量为2亿条记录左右。test2的数据量在10万条记录左右。 
各位能否提供一个高效的方法完成此功能。 这么大的数据量能查询出来就不错了.

Answer 3

表test2中id=3，value=‘nothing’的记录修改为id=7，value=‘nothing’。这样才能符合我说的意思。

Answer 4

首先 表test2中id=3，value=‘nothing’的记录修改为id=7，value=‘nothing’，这样才对。
我做了测试，结果还是不对。
SQL> SELECT     a.name,b.value 
  2  FROM       test1 a,test2 b 
  3  WHERE      a.id(+)=b.id 
  4  GROUP BY a.name,b.value 
  5  HAVING count(decode(a.id,null,1))=0; NAME       VALUE
---------- ----------
a          good
a          badElapsed: 00:00:00.01

Answer 5

select max(a.name),b.value 
from test1 a,test2 b 
where a.id(+)=b.id 
group by b.value 
having count(decode(a.id,null,1))=0

Answer 6

现在可以完成功能。但是有个问题：如果有n个name，对应1个value。并且满足关系，一个max的结果就会导致只能在n个有效结果集中得到1条记录。

Answer 7

为了解决n个nanme对应1个value的问题。我又添加几个字段。
test1：
NAME               ID
---------- ----------
a                   1
a                   2
a                   3
a                   4
b                   1
b                   2
b                   3
b                   4
c                   1
c                   2
c                   3
c                   4
c                   5
在这种情况下name='a','b'都对应value='good'，name='c'的数据是为了验证1个name对应n个value的情况。
代码如下：
SELECT a.name, b.value
FROM test1 a,
(
 SELECT b.id, b.value, COUNT(*) OVER(PARTITION BY b.value) AS cnt
 FROM test2 b
)b
WHERE a.id = b.id
GROUP BY a.name, b.value
HAVING COUNT(*) = MIN(b.cnt);结果如下：
NAME       VALUE
---------- ----------
b          good
a          good
c          good
c          bad这样应该能得到正确的结果。
其中test2是几十万的数据量，对这个数据量做row_number()的效率不会很差。(也可以在实际应用中先做个临时表)。
但最后还是要在2亿的数据量上进行group by。我想到的方法是没办法避免group by操作的。
这是我能想到的最有效的方法了。
我们要群策群力看看有没有更高效的方法。可能还有些特殊情况我没有考虑清楚，如有遗漏的地方还请各位指正，争取把这个问题完全解决。

Answer 8

顶一下2亿级的表要完成这样的查询确实很有难度
要尽量少对大表进行遍历读取，可以利用下test2 这个相对较小的表
要避免group by或distinct操作很难