SQL: how to find an unused primary key

Question

I have a table s> 1'000'000 records; this table is referenced from approximately 130 other tables. My problem is that many of these 1-mio records are old and unused.

What is the way to quickly find records that are not referenced by any of the other tables? I do not like to do

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?

Thanks everyone!

Answer 1

You can do it:

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

Answer 2

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 can 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 won't take much time, as in the solution above.

Answer 3

Not sure about the effectiveness, but:

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

If your concern allows the database to continue normal operations while you make the house, you can break it down into several stages:

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

as many as you need, and then:

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

Of course, sometimes processing a large quantity takes a lot of time.

Answer 4

I like @Patrick's answer above, but I would like to add to this.
Instead of manually creating a query in 130 steps, you can create these INSERT statements by scanning sysObjects, find key relationships, and generate your INSERT statements.

This will not only save you time, but also help you know exactly whether you have covered all the tables - maybe 131 or only 129.

Answer 5

I am inclined to answer Marcelo Cantos (and supported him), but here is an alternative in an attempt to get around the problem of the lack of indexes on 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 offer an Oracle order to minimize its effort. Unfortunately, Oracle will compile this to be very similar to Marcelo Cantos' answer, and it may not work differently.