从一个表中删除连接到另一个表中的记录

Question

我有两个表，一个有212,000条记录（不推荐使用的记录），另一个有10,500,000条记录

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

Answer 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 join和full table scan，以获得可接受的性能。

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

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

简单地将一个连接放到小表中不能复制大表中的行，因为这是一个独特的反差

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

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 FROM big 
    WHERE EXISTS (SELECT null
                  FROM delta
                  WHERE delta.id = big.id and delta.version = big.version
                 ) 

不需要索引，您应该期待一个带有哈希联接右半的执行计划，这意味着这两种方法实际上并没有什么不同。

测试样本数据

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);