Trigger performance and exception handling

From: Lauren Smith <laurens_at_signs.portents.com>
Date: Fri, 15 Mar 2002 19:14:42 GMT
Message-ID: <Pine.NEB.4.21.0203151412180.15120-100000_at_portents.ne.client2.attbi.com>


Why is it when I insert a million records using the following trigger code (at the bottom of this email; purposefully causing an exception) it takes 34 minutes, but when I replace the 'a' with a '1', it only takes 3.5 minutes?

I would be fine with some performance degredation for using an exception, but that much? How does Oracle handle exceptions within triggers that would cause such problems? (In practice, we are using similar to_number logic throughout our code to determine whether a string can be a number.)

Thanks,

Lauren Smith

CREATE OR REPLACE TRIGGER PBDS.LLTEST_DUMMY BEFORE INSERT OR UPDATE ON PBDS.LLTEST
FOR EACH ROW

--
BEGIN
   if to_number('a') = 'a' then
      null;
   end if;
exception when others then
   null;
END;
/
Received on Fri Mar 15 2002 - 20:14:42 CET

Original text of this message