代码之家 › 专栏 › 技术社区 › bjan

查找不同匹配记录的最快方法

rdbms oracle sql-server sql

bjan · 技术社区 · 13 年前

我有两张表A和B。这两张表都有相同的结构。我们在这两者之间找到了匹配的记录。这是剧本

CREATE TABLE HRS.A
(
F_1 NUMBER(5,0),
F_2 NUMBER(5,0),
F_3 NUMBER(5,0)
);

CREATE TABLE HRS.B
(
F_1 NUMBER(5,0),
F_2 NUMBER(5,0),
F_3 NUMBER(5,0)
);

INSERT INTO hrs.a VALUES (1,1000,2000);
INSERT INTO hrs.a VALUES (2,1100,8000);
INSERT INTO hrs.a VALUES (3,4000,3000);
INSERT INTO hrs.a VALUES (4,2000,5000);
INSERT INTO hrs.a VALUES (5,5000,3000);
INSERT INTO hrs.a VALUES (6,6000,6000);
INSERT INTO hrs.a VALUES (7,3000,7000);
INSERT INTO hrs.a VALUES (8,1100,9000);

INSERT INTO hrs.b VALUES (1,4000,2000);
INSERT INTO hrs.b VALUES (2,6000,8000);
INSERT INTO hrs.b VALUES (3,1000,3000);
INSERT INTO hrs.b VALUES (4,2000,5000);
INSERT INTO hrs.b VALUES (5,8000,3000);
INSERT INTO hrs.b VALUES (6,1100,6000);
INSERT INTO hrs.b VALUES (7,5000,7000);
INSERT INTO hrs.b VALUES (8,1000,9000);

查找匹配的记录

SELECT a.F_1 A_F1, b.F_1 B_F1 FROM HRS.A, HRS.B WHERE A.F_2 = B.F_2

后果

现在我想分别删除两列中的重复条目,例如1在A_F1中重复(与B_F1无关),因此将删除第3行(1-3)和第8行(1-8)。现在6在B_F1中重复(与A_F1无关),因此第5行(8-6)和第6行(2-6)将被删除。最终结果应为

现在最重要的部分 ,这两张表包含 每个500000条记录 。我首先找到这些匹配的记录并将其插入到临时表中,然后从第一列中删除重复记录,然后从第二列中删除,然后从临时表中选择所有记录。这太慢了。我如何才能尽快实现这一目标?

编辑#1

我多次执行以下语句,在每个表中生成4096条记录

INSERT INTO hrs.a SELECT F_1 + 1, F_2 + 1, 0 FROM hrs.a;
INSERT INTO hrs.b SELECT F_1 + 1, F_2 + 1, 0 FROM hrs.b;

现在我执行了所有的答案并找到了这些

Rachcha     9.11 secs   OK
techdo      1.14 secs   OK
Gentlezerg  577  msecs  WRONG RESULTS
Justin      218  msecs  OK

就连@Justin也用了37.69秒,每条记录有65536条(总计131072条)

正在等待更优化的答案,因为实际记录数为1000000:)

以下是基于Justin回答的查询执行计划

enter image description here

5 回复 | 直到 13 年前

TechDo 13 年前

请尝试:

select A_F1, B_F1 From(
  SELECT a.F_1 A_F1, b.F_1 B_F1, 
    count(*) over (partition by a.F_1 order by a.F_1) C1,
    count(*) over (partition by b.F_1 order by b.F_1) C2
  FROM HRS.A A, HRS.B B WHERE A.F_2 = B.F_2
)x 
where C1=1 and C2=1;

来一杯怎么样 INNER JOIN 相反请使用此查询进行检查。

select A_F1, B_F1 From(
  SELECT a.F_1 A_F1, b.F_1 B_F1, 
    count(*) over (partition by a.F_1 order by a.F_1) C1,
    count(*) over (partition by b.F_1 order by b.F_1) C2
  FROM HRS.A A INNER JOIN HRS.B B ON A.F_2 = B.F_2
)x 
where C1=1 and C2=1;

Justin SventoryMang 13 年前

查询:

SQLFIDDLEExample

SELECT a.f_1 AS a_f_1, 
       b.f_1 AS b_f_1
FROM a JOIN b ON a.f_2 = b.f_2
WHERE 1 = (SELECT COUNT(*)
           FROM a aa JOIN b bb ON aa.f_2 = bb.f_2
           WHERE aa.f_1 = a.f_1 )
AND 1 = (SELECT COUNT(*)
           FROM a aa JOIN b bb ON aa.f_2 = bb.f_2
           WHERE bb.f_1 = b.f_1 )

结果:

| A_F_1 | B_F_1 |
-----------------
|     3 |     1 |
|     6 |     2 |
|     4 |     4 |
|     5 |     7 |

Gentlezerg 13 年前

根据@techdo的回答,我认为这可以更好:

select A_F1, B_F1 From(
  SELECT a.F_1 A_F1, b.F_1 B_F1,a.F_2,
    count(*) OVER(PARTITION BY A.F_2) C
  FROM HRS.A A, HRS.B B WHERE A.F_2 = B.F_2
)x 
where C=1 ;

多行的存在是由于相同的f_2。此SQL只有一个计数。。结束了,所以你说你有大量的数据,我认为这会更快一点。

Rachcha 13 年前

我有答案了。

看看这个 fiddle 在这里

我使用了以下代码:

WITH x AS (SELECT a.f_1 AS a_f_1, b.f_1 AS b_f_1
             FROM a JOIN b ON a.f_2 = b.f_2)
SELECT *
  FROM x x1
 WHERE NOT EXISTS (SELECT 1
                     FROM x x2
                    WHERE (x2.a_f_1 = x1.a_f_1
                           AND x2.b_f_1 != x1.b_f_1)
                       OR (x2.a_f_1 != x1.a_f_1
                           AND x2.b_f_1 = x1.b_f_1)
                  )
;

编辑

我习惯于在14毫秒内运行以下代码 SQL fiddle 。我删除了通用表表达式,并观察到查询性能有所提高。

SELECT a1.f_1 AS a_f1, b1.f_1 AS b_f1
  FROM a a1 JOIN b b1 ON a1.f_2 = b1.f_2
 WHERE NOT EXISTS (SELECT 1
                     FROM a a2 JOIN b b2 ON a2.f_2 = b2.f_2
                    WHERE (a2.f_1 = a1.f_1
                           AND b2.f_1 != b1.f_1)
                       OR (a2.f_1 != a1.f_1
                           AND b2.f_1 = b1.f_1))
;

输出:

A_F_1   B_F_1
3           1
6           2
4           4
5           7

bjan 13 年前

这些解决方案中的每一个都需要时间,最好的一个(Justin)花了将近45分钟,甚至没有获得200万张唱片。我最终在一个临时表中插入了匹配的记录,然后删除了重复的记录,我发现它比这些使用此数据集的解决方案快得多。