All entries for Thursday 08 June 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)

: 08 Jun 2006 14:27 |  Tags: Oracle |  Comments (1) |  Close comments |  Report a problem