SQL:如何找到未使用的主键

发布于 2024-10-24 17:45:24 字数 400 浏览 2 评论 0原文

我有一张桌子,上面有 > 1'000'000 个条目;该表引用了大约 130 个其他表。我的问题是,很多 1-mio-entries 都是旧的且未使用的。

找到未被任何其他表引用的条目的最快方法是什么?我不喜欢做 a

select * from (
select * from table-a TA
minus
select * from table-a TA where TA.id in (
select "ID" from (
   (select distinct FK-ID "ID" from table-b)
union all
  (select distinct FK-ID "ID" from table-c)
...

有没有更简单、更通用的方法?

谢谢大家!

I've got a table with > 1'000'000 entries; this table is referenced from about 130 other tables. My problem is that a lot of those 1-mio-entries is old and unused.

What's the fastet way to find the entries not referenced by any of the other tables? I don't like to do a

select * from (
select * from table-a TA
minus
select * from table-a TA where TA.id in (
select "ID" from (
   (select distinct FK-ID "ID" from table-b)
union all
  (select distinct FK-ID "ID" from table-c)
...

Is there an easier, more general way?

Thank you all!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

飘然心甜 2024-10-31 17:45:24

你可以这样做:

select * from table_a a
 where not exists (select * from table_b where fk_id = a.id)
   and not exists (select * from table_c where fk_id = a.id)
   and not exists (select * from table_d where fk_id = a.id)
   ...

You could do this:

select * from table_a a
 where not exists (select * from table_b where fk_id = a.id)
   and not exists (select * from table_c where fk_id = a.id)
   and not exists (select * from table_d where fk_id = a.id)
   ...
久夏青 2024-10-31 17:45:24

try :

select a.* 
from table_a a
left join table_b b on a.id=b.fk_id
left join table_c c on a.id=c.fk_id
left join table_d d on a.id=d.fk_id
left join table_e e on a.id=e.fk_id
......
where b.fk_id is null 
and c.fk_id is null 
and d.fk_id is null 
and e.fk_id is null 
.....

你也可以尝试:

select a.* 
from table_a a
left join
    (select b.fk_id from table_b b union
     select c.fk_id from table_c c union
     ...) table_union on a.id=table_union.fk_id
where table_union.fk_id is null

这是更面向 SQL 的,并且不会像上面的解决方案那样永远花费时间。

try :

select a.* 
from table_a a
left join table_b b on a.id=b.fk_id
left join table_c c on a.id=c.fk_id
left join table_d d on a.id=d.fk_id
left join table_e e on a.id=e.fk_id
......
where b.fk_id is null 
and c.fk_id is null 
and d.fk_id is null 
and e.fk_id is null 
.....

you might also try:

select a.* 
from table_a a
left join
    (select b.fk_id from table_b b union
     select c.fk_id from table_c c union
     ...) table_union on a.id=table_union.fk_id
where table_union.fk_id is null

This is more SQL oriented and it will not take forever like the above solution.

情徒 2024-10-31 17:45:24

不确定效率,但是:

select * from table_a 
where id not in ( 
    select id from table_b
    union
    select id from table_c )

如果您关心的是在进行内务管理时允许数据库继续正常操作,您可以将其分为多个阶段:

insert into tblIds 
    select id from table_a
    union
    select id from table_b

根据需要多次,然后:

delete * from table_a where id not in ( select id from tableIds )

当然,有时进行大量处理需要花费一些时间很多时间。

Not sure about efficiency but:

select * from table_a 
where id not in ( 
    select id from table_b
    union
    select id from table_c )

If your concern is allowing the database to continue normal operations while you do the house keeping you could split it into multiple stages:

insert into tblIds 
    select id from table_a
    union
    select id from table_b

as may times as you need and then:

delete * from table_a where id not in ( select id from tableIds )

Of course sometimes doing a lot of processing takes a lot of time.

落花随流水 2024-10-31 17:45:24

我喜欢上面 @Patrick 的回答,但我想补充一下。
您可以通过扫描 sysObjects、查找关键关系并生成 INSERT 语句来构建这些 INSERT 语句,而不是手动构建 130 步查询。

这不仅可以节省您的时间,还可以帮助您确定是否已覆盖所有表格 - 可能有 131 个,也可能只有 129 个。

I like @Patrick's answer above, but I would like to add to that.
Rather than building the 130-step query by hand, you could build these INSERT statements by scanning sysObjects, finding key relations and generating your INSERT statements.

That would not only save you time, but should also help you to know for sure whether you've covered all the tables - maybe there are 131, or only 129.

行雁书 2024-10-31 17:45:24

我倾向于马塞洛·坎托斯的回答(并已投赞成票),但这里有一个替代方案,试图规避外键上没有索引的问题......

WITH
  ids_a AS
(
  SELECT id FROM myTable
)
,
  ids_b AS
(
  SELECT id FROM ids_a WHERE NOT EXISTS (SELECT * FROM table_a WHERE fk_id = ids_a.id)
)
,
  ids_c AS
(
  SELECT id FROM ids_b WHERE NOT EXISTS (SELECT * FROM table_b WHERE fk_id = ids_b.id)
)
,
...
,
  ids_z AS
(
  SELECT id FROM ids_y WHERE NOT EXISTS (SELECT * FROM table_y WHERE fk_id = ids_y.id)
)

SELECT * FROM ids_z

我想做的就是建议一个为了让甲骨文尽量减少其努力。不幸的是,Oracle 会将其编译为与 Marcelo Cantos 的答案非常相似的内容,并且它的性能可能没有任何不同。

I'm inclined to Marcelo Cantos' answer (and have upvoted it), but here is an alternative in an attempt to circumvent the problem of not having indexes on the foreign keys...

WITH
  ids_a AS
(
  SELECT id FROM myTable
)
,
  ids_b AS
(
  SELECT id FROM ids_a WHERE NOT EXISTS (SELECT * FROM table_a WHERE fk_id = ids_a.id)
)
,
  ids_c AS
(
  SELECT id FROM ids_b WHERE NOT EXISTS (SELECT * FROM table_b WHERE fk_id = ids_b.id)
)
,
...
,
  ids_z AS
(
  SELECT id FROM ids_y WHERE NOT EXISTS (SELECT * FROM table_y WHERE fk_id = ids_y.id)
)

SELECT * FROM ids_z

All I'm trying to do is to suggest an order to Oracle to minimise its efforts. Unfortunately Oracle will compile this to comething very similar to Marcelo Cantos' answer and it may not performa any differently.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文