All entries for Thursday 08 June 2006
June 08, 2006
Interesting oracle funniness
So I have a table content which has 30500 rows, it has a one to many relationship with content_fetcher, which has 50700 rows.
I have removed a number of rows from content, and I now want to remove the orphaned rows from content_fetcher (cascade delete; ha, who needs it :)).
So, trivially easy SQL to find out the number of orphaned content_fetchers…
select count(id) from content_fetcher where content_id not in (select id from content)
times out :( Why?
Bizarrely, calculating the number of content_fetchers that are not orphaned works:
select count(id) from content_fetcher where content_id in (select id from content)
(30622 rows BTW)
so, we know how many valid content_fetchers there are… well, counting the number of content_fetchers that are not in that set, surely that will hit the same "feature"?:
select count(id) from content_fetcher where id not in ( select id from content_fetcher where content_id in (select id from content) );
Nope; it works. 20103 rows.
Go figure….
(deletion took 22 seconds BTW)