Mar 31 2010 10:46am from Peter Pulse @uncnsrd
I see it from a different point of view.. if I am going to pay a
kazillion dollars for a database and people to run it, it damn well
better have referential integrity. That is one of the MAJOR selling
points of these systems isn't it???
Ok, well yes and no. It's a major selling point of RDBMS in general I agree with that. I don't agree that it's a major selling point of Oracle in specific, because it's become a very commoditized feature let's face it. Most of the major open source RDBMSs implement it... the value proposition of Oracle is further up the pyramid, with things like partitioning, and RAC, and replication, and all those fancypants SQL extensions, like analytical queries (some of which are standard ANSI SQL now, btw.)
In any case, foreign keys sometimes just don't work, because they can impose unpredictible lock orderings on your queries. Every time you issue DML on a constrained table, Oracle (and probably every other RDBMS suffers the same problem) spawns a child cursor/query against the parent/child tables to check indexes and enforce constraints. That child query creates the lock-order problem and as near as I've ever been able to tell, the ordering is simply undefined and can't be relied on.
So most likely FK constraints will work just fine for you for months or years, but eventually as your load creeps up and your schema becomes more complex and you have more and more disparate queries hitting it, you will start running into ORA-00060 (deadlock.) And most likely the problem is universal across RDBMS.
When this does happen, you can spend a lot of time scratching your head, analyzing Oracle dumps, and trying to fix your queries to consistify the locking order. But you will likely fail - so you either have to code around the ORA-00060 with retries, or just drop your constraints. Best not to go down the analysis route - it's so unlikely to work - just retry queries or drop constraints.
This is one reason you should never write code that DEPENDS on the presence on FK constraints in some what, particularly code that relies on CASCADE DELETE. Code must continue to function properly if the constraints have to be dropped, some day.
some what = some way
Speaking of our genius DBA, I was somewhat surprised that sybase didn't have cascade delete, and because of some reason I forget I wanted to delete stuff in the cascade delete order, but the foreign key constraints the DBAs put on my tables didn't allow for that.
So my sprightly (spritely?) dba told me he'd write a trigger to implement cascade delete
Scratching my head I wondered how this was going to work without removing the fk constraints. After much banging his head, he let it drop, as in gave up, and I still have to delete my stuff backwards.
Shouldn't be that hard. But it is, it always is.
I recently reached a milestone in my life. Since I was about 23 or so I've never run less than 2 machines, one for my use and one fo the websites I run and mail and whatever else. Well finally I consolidated, and I have everything on one big ass machine.
There are many downsides, but this is the way it's going to be for a while, and I think my most pressing problem is that I have no online machine to back this machine up to.
I used to rsync back and forth between the two. Now there's nowhere to rsync to.
This machine has raid0 via linux so it's not completely at risk, but I'd still like somewhere else to go.
so I was thinking the memorystick angle. I see you can buy a 64gig stick on ebay for about $20-$30. Is this reasonable or is this crap from china that will write once read never?
Or does anybody have a better idea? I've got about 36gig to back up at the moment.
Sad my whole online life fits in 36gig.
That trigger might actually work on Oracle because of the deferred constraints feature.
I bought a 1TB external disc for backup, but Steve Ballmer probably made me do that.
Check it out if you use Windows and haven't seen it.
That is kind of interesting. It might come in handy, if I can ever get away from XP.
I wonder if it's kind of like an rsync.
Even so, out of the box robocopy is the only way on Windows to copy files without bothering to rewrite existing ones -- Very useful for large interrupted file transfers.
It probably doesn't send binary differences, as rsync does.
so the legacy found anotherway:
dolby mobile so its not just tapes and home cinema stuff...
I hear it every day when my kids turn on the Nintendo Wii. :)
well one of its shortcommings: no digital audio out...else it would be a perfect dvd player. (hacked and with mplayer)