Stephan Szabo wrote:
On Fri, 15 Aug 2003, Andreas Pflug wrote:
Stephan Szabo wrote:
That really needs to be rewritten to do a single check over the table
rather than running the constraint for every row. I keep meaning to get
around to it and never actually do. :( I'm not sure that in practice
you'll get a better plan at restore time depending on what the default
statistics give you.
This is clearly a case for a statement level trigger, as soon as
affected rows can be identified.
Well, I think single inserts might be more expensive (because the query is
more involved for the table joining case) using a statement level trigger,
so we'd probably want to profile the cases.
This really depends. If a constraint is just a check on the
inserted/updated column, so no other row needs to be checked, there's no
faster way then the current row trigger. But FK constraints need to
execute a query to retrieve the referenced row, and every RDBMS prefers
to execute a single statement with many rows over many statements with a
single row, because the first will profit from optimization. And even if
only a single row is inserted or updated, there's still the need to
lookup the reference.
Regards,
Andreas
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
