Re: Foreign Keys Constraints, perforamance analysis

> No,
> I compare
> DELETE FROM person;
> against
> DELETE FROM person;
> DELETE FROM married;
> DELETE FROM child;
>
> Which I think has very much to do with performane of real-worl
applications
> i think. I often think of Accounts, where there are numerous records
stored
> for this account - which should be deleted when the account is deleted.

It doesn't unless you delete all your people alot (as Tom said).

There's a BIG difference between
delete from person where name='foo' compared to
delete from person where name='foo'; delete from married where ... ; delete
from child where ...;
and
delete from person; compared to
delete from person; delete from married; delete from child;

In the first case, the system sees either 1 statement that expands into 3
statements effectively versus 3 statements.  Not too different.

In the second case the system sees 1 statement + 1 statement per row versus
3 statements.
Very different, because it doesn't know it's going to be deleting all of the
rows so it's probably going to choose to index scan to find the matching
rows for each row per each row in person versus knowing before hand to
delete them all.

In addition, with match unspecified, these two behaviors are also not
guaranteed to be the same.  With NULLs in the FK fields, you can have rows
that shouldn't get deleted when you delete all of the PK rows.  ("At least
one of the values of the referencing columns in R1 shall be a null value, or
the value of each referencing column in R1 shall be equal to the value of
the corresponding referenced column in some row of the referenced table....
let matching rows be all rows in the referencing table whose referencing
column values equal the corresponding referenced column values for the
referential constraint")

There are problems, and it would be nice to figure out a way to combine
actions and checks when a large number of changes are seen (of course how do
you define a large number, but...) to get around some of these bulk cases.


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com