All 1 entries tagged Oracle

View all 121 entries tagged Oracle on Warwick Blogs | View entries tagged Oracle at Technorati | There are no images tagged Oracle on this blog

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)


February 2020

Mo Tu We Th Fr Sa Su
Jan |  Today  |
               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   

Search this blog

Tags

Galleries

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

Loading…
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder
© MMXX