Re: Triggers - How expensive when set on heavily loaded tables ?

row row row your boat......row level that is

BTW - If you were wondering more about how I tested. I had about 10,000 rows in table_a, and ran the statement "insert into table_b as select * from table_a". I did it without, and with one or more triggers on table_b. Maybe triggers are faster in v9 as pl/sql gets faster with each release, but I doubt that it's down to the "almost no impact" level.

Cheers

                                                                                                                                 
                      jkstill_at_cybcon.co                                                                                          
                      m                        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>             
                      Sent by:                 cc:                                                                               
                      ml-errors_at_fatcity        Subject:  Re: Triggers - How expensive when set on heavily loaded tables ?        
                      .com                                                                                                       
                                                                                                                                 
                                                                                                                                 
                      07/24/2003 09:09                                                                                           
                      PM                                                                                                         
                      Please respond to                                                                                          
                      ORACLE-L                                                                                                   
                                                                                                                                 
                                                                                                                                 

Brian,

Were those row level or statement level triggers?

Jared

On Wed, 23 Jul 2003 Brian_P_MacLean_at_eFunds.Com wrote:

>
> I did some trigger timings about a year ago on v8.1.7.4. I did the
testing
> using a trigger with only 1 statement in it, and that statements was
> "null;". I found that my transaction timing increased by 100% for the
> first trigger added, and 25% for every trigger after that. So that math
> breaks down this way:
>
> The base transaction took 60 seconds;
> With one before insert trigger with a "null;" statement in it, it
now
> took 120 seconds;
> I added one more after insert trigger with a "null" statement in
it,
> the total time was 150 seconds;
>
> Kind of makes ya rethink/realize what the true cost of all those before
> insert/update triggers we have added over the years, that do nothing more
> than update the add_date and change_date columns. I know I'll never do
it
> again.
>
> Now in your case, maybe the trigger is the right answer. But we can't
make
> that decision for you.....YOU HAVE TO TEST IT FOR YOURSELF!
>
>
>
>
>
>
>

> "VIVEK_SHARMA"

> <VIVEK_SHARMA_at_inf To: Multiple
recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> osys.com> cc:

> Sent by: Subject: Triggers - How
expensive when set on heavily loaded tables ?
> ml-errors_at_fatcity

> .com

>

>

> 07/23/2003 09:14

> PM

> Please respond to

> ORACLE-L

>

>

>
>
>
>
>
> Qs 1) Whenever an Account ID is transacted - INSERT occurs into a table
> TRAN_TBL as part of the transaction , Additionally the Account ID is
> checked for existence in another table say TABLE_1 . If found , a record
is
> inserted into yet another table say TABLE_2 .
>
> Qs. Operations involving TABLE_1 & TABLE_2 if managed using triggers ,
How
> expensive in CPU & performance will it be ?
>
> NOTE - TRAN_TBL undergoes very large volumes of Concurrent OLTP INSERT
> transaction .
>
>
> Qs 2) If an alert is to be raised on addition of a new record to TABLE_2
:-
> a) Can trigger be used ?
> b) Should a cron job running every 5 min. look at TABLE_2 & based on the
> time criteria generates the alert ?
>
> Thanks
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infosys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L