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)

- One comment Not publicly viewable

  1. Mathew Mannion

    I noticed this the other day. In BlogBuilder, I had a hunch that there might be a number of orphaned comments, which may have contributed to some issues with the My Comments page (such as detailed in this forum post).

    I must admit that after

    select * from comments where entry_id not in (select id from entries)

    timed–out (and crashed the program I was using to run it) that I eventually gave up.


    08 Jun 2006, 14:47

Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

June 2006

Mo Tu We Th Fr Sa Su
May |  Today  | Jul
         1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30      

Search this blog



Most recent comments

  • Interesting… While I'm not completely convinced in such microbenchmarks, I'm pretty sure that 1ms … by Alexander Snaps on this entry
  • Hello. I bought the book yesterday. I was trying to find the source code for chapter 11 and chapter … by Suleman on this entry
  • http://woosight.net/account/login?username=demo by live mashup demo on this entry
  • Thanks mate ….. This blog was really helpful. by Maaz Hurzuk on this entry
  • Ty. Not directly helpful for my problem, but pointed me in the right direction. You will also get th… by Mike E. on this entry

Blog archive

Not signed in
Sign in

Powered by BlogBuilder