sql - 从一个连接到另一个表 SQL 的表中删除记录
问题描述
我有两张表,一张有 212,000 条记录(已弃用的记录),另一张有 10,500,000 条记录
我想在 id 和 version_number 字段上加入这两个表,因为这两个表都有这些字段。我希望可以从连接表中删除匹配的记录(从连接表中),即从 10,500,000 条记录中删除所有 212,000 条记录
我想知道使用 Oracle SQL 的最佳方法是什么?我已经看到使用单个字段使用内部连接的示例,并且使用删除语句从表 2 中删除 table1,但没有看到使用两个字段的示例(在连接中)。
在删除记录之前使用外部联接是否有意义?我在想这可能有助于我跟踪已删除的内容(如果可能)
解决方案
您不需要使用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
withfull table scan
以获得可接受的性能。
基本上有两种选择如何做DELETE
可更新的连接视图
请注意,在这种情况下,您的小表必须具有唯一索引 ID, VERSION
(或主键)
create unique index delta_idx on delta(id,version);
相反,BIG 表不应该有这样的约束。这很重要,因为它清楚地表明您的 BIG 表是连接视图中唯一的一个键保留表。
由于独特的约束,简单地加入小表不能复制大表中的行
请参阅此处有关更新联接视图的更多信息
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
使用 EXISTS 删除
如果您的小表没有主键(即它可以包含相同的重复行ID and VERSION
),您必须回退到其他答案中提出的解决方案。
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);
推荐阅读
- javascript - 如何创建 BTC 到 USD 计算器,反之亦然?
- python - 在 Tkinter 中按下按钮时,如何使用函数执行多个任务?
- python - 如何通过不和谐机器人提及用户 - python
- java - 使用 ANT 从嵌套的 zip 文件中解压缩特定的 zip 文件
- c# - 仅在不同文件夹中包含特定 nuget 包的 .xml 文件
- css - 状态更新时反应滚动位置跳跃
- swift - 如何设置约束以侵入另一个视图
- websphere - 如何对与 ibm bpm 实例相关的文件信息进行 cmis 查询?
- google-app-engine - Google App Engine 支持带有 A 记录的裸域这一事实是否意味着 GAE IP 实际上是静态的?
- google-maps-android-api-2 - 通往公共谷歌地图的游乐区小径