删除连接到另一个表SQL的一个表中的记录 [英] Deleting records from one table joined onto another table SQL

问题描述

我有两个表，一个包含211,000条记录(不推荐使用的记录)，另一个包含10,500,000条记录

我想在id和versionnumber字段上连接这两个表，因为这两个表都有这些字段。我希望从连接表中删除匹配的记录(来自连接表)，即从10,500,000条记录中删除所有212,000条记录

我想知道使用Oracle SQL解决此问题的最佳方法是什么？我已经看到了 示例中，已使用单个字段使用内部联接，并且已使用DELETE语句从表2中删除表1，但未看到使用了两个字段的表1(在联接中)。

在删除记录之前使用外部联接是否有意义？我在想，如果可能的话，这可能会帮助我追踪被删除的内容

推荐答案

您不需要使用OUTER JOIN，只需检查将分别显示多少行。不会被删除吗。

此类查询的示例见下文(我使用答案末尾提供的生成测试数据)

with del as (
select delta.id, delta.version,
decode(big.id,null,0,1) is_deleted
from delta
left outer join big 
on delta.id = big.id and delta.version = big.version
)
select is_deleted, count(*) cnt, max(id||'.'||version) eg_id_vers
from del
group by is_deleted;

IS_DELETED        CNT  EG_ID_VERS                                                                   
---------- ---------- ----------
         1      20000 99995.0   
         0         20 100100.0   

对于您的数据大小，您应该在两个表上使用HASH JOINWITHfull table scan以获得可接受的性能。

执行DELETE操作基本上有两种选择

可更新联接视图

请注意，在这种情况下，您的小表必须在ID, VERSION上有唯一索引(或主键)

create unique index delta_idx on delta(id,version);

相反，大表不应该有这样的约束。这一点很重要，因为它清楚地表明您的大表是联接视图中唯一一个保留键的表。

由于UNIQUE约束

，对小表的简单PUT联接无法复制大表中的行

请参阅here有关更新联接视图的详细信息

delete from 
(
select delta.id, delta.version, big.id big_id, big.version
from big 
join delta 
on delta.id = big.id and delta.version = big.version
)

上面的delete从BIG表中删除行，因为这是唯一的保留键的表(请参阅上面的讨论)

此DML通向HASH JOIN

删除With Existes

如果您的小表没有主键(即它可以包含具有相同ID and VERSION的重复行)，则必须后备到other answer中建议的解决方案。

DELETE FROM big 
    WHERE EXISTS (SELECT null
                  FROM delta
                  WHERE delta.id = big.id and delta.version = big.version
                 ) 

不需要索引，您应该期待HASH JOIN RIGHT SEMI的执行计划，这意味着这两种方法并不是真的不同。

测试样本数据

create table big as
select 
trunc(rownum/10) id, mod(rownum,10) version,
lpad('x',10,'Y') pad
from dual connect by level <= 1000000;

/* the DELTA table has 50 times less rows,
allow some rows out of range of the BIG table - those rows will not be deleted **/
drop table delta;
create table delta as
select 
trunc(rownum*50/10) id, mod(rownum*50,10) version
from dual connect by level <= 1001000/50;

create unique index delta_idx on delta(id,version);

这篇关于删除连接到另一个表SQL的一个表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！