Re: Oracle triggers = poor performance ??

It's all relative.

Yes, triggers do slow things down, but the effects can range from extreme to invisible.

Put a simple trigger onto an OLTP application and the user will not notice the extra fractions of a second that the database spends doing the work of the trigger.

Put a simple trigger on for the duration of a 10,000,000 row batch load, and (a) you can't run it unrecoverably so it goes 3 times as slowly, and (b) the trigger does its bit 10,000,000 times and probably doubles the run time again.

Bottom line: if the trigger is the 'sensible' way of doing a job that has to be done the cost of the trigger will be a reasonable cost.

As a guideline, though, if Oracle does it as a built-in then writing a trigger is probably less efficient (after all, Oracle knows how to take short-cuts and cheat on built-ins).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Edmund Landgraf wrote in message ...
>I've heard Oracle triggers really impact performance. I'm using ErWin and
>am trying to decide if I should use triggers to enforce RI in addition to
my
>foreign key constraints. With triggers, for example, I have the ability to
>cascade, restrict or orphan child nodes when a parent is either inserted,
>updated, or deleted. Can I get that flexibility with keywords off of the
>constraints (e.g. cascade delete)???
>
>Here's a typical cascade delete trigger: (table address has two children,
>useraddress and propertyaddress)
>
>Thoughts????
>
>thx in adv.
>-Ed
>
>
>
>
>
>declare numrows INTEGER;
>begin
> /* ERwin Builtin Thu Jul 29 13:47:02 1999 */
> /* Address R/268 UserAddress ON PARENT DELETE CASCADE */
> delete from UserAddress
> where
> /* %JoinFKPK(UserAddress,:%Old," = "," and") */
> UserAddress.AddressID = :old.AddressID;
>
> /* ERwin Builtin Thu Jul 29 13:47:02 1999 */
> /* Address R/266 PropertyAddress ON PARENT DELETE CASCADE */
> delete from PropertyAddress
> where
> /* %JoinFKPK(PropertyAddress,:%Old," = "," and") */
> PropertyAddress.AddressID = :old.AddressID;
>
>
>-- ERwin Builtin Thu Jul 29 13:47:02 1999
>end;
>
>
Received on Thu Jul 29 1999 - 16:51:53 CDT