1 2 Previous Next 21 Replies Latest reply on Oct 18, 2013 12:20 PM by yxes2013 Go to original post
      • 15. Re: Which is efficient?

        yxes2013 wrote:


        Hi all,


        Why is this:


        SQL> select scn_to_timestamp(ora_rowscn) timestamp from EMP;

        select scn_to_timestamp(ora_rowscn) timestamp from EMP


        ERROR at line 1:

        ORA-08181: specified number is not a valid system change number

        ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

        That's because you did not read the 1st comment of Tom Kyte from the article I linked earlier.


        >I tried to recommend trigger but it was disapproved by the management since the perfornance is very critical now. They said trigger will mirror the table and hence will have big impact on performance?

        Are you asking ? Anyway, they have to choose, you have to think what you could do to make "them" change their mind (benchmark).



        • 16. Re: Which is efficient?

          Thanks blue,


          How do I know if my table contents in 1 day is within the valid SCN?



          • 17. Re: Which is efficient?

            Hi Nic,


            Supposing my tables is only doing pure INSERT (NO UPDATE, NO DELETE) will the ora_rowscn be reliable?



            • 18. Re: Which is efficient?
              Girish Sharma

              I created a sample table like below and inserted couple of rows:

              create table table1(id number,amount number);


              insert into table1 values (1,20);

              insert into table1 values (2,25);

              insert into table1 values (3,-5);


              To store date and sum of amount of table1, I created table2 like below:

              create table table2(run_date date,sum_of_amount number);


              Now, I am creating a procedure who will sum amount and store it into table2.


              create or replace procedure prc_sum_amount_table1


              execsql varchar2(100);


              execsql:='insert into table2 values ((select sysdate from dual),(select sum(amount) from table1))';

              execute immediate execsql;




              Now, I am creating and submitting a job which will run every day every one hour.



                DBMS_SCHEDULER.create_job (

                  job_name => 'sum_amount_table1',

                  job_type              => 'STORED_PROCEDURE',

                  job_action            => 'prc_sum_amount_table1',

                repeat_interval       => 'FREQ=HOURLY; INTERVAL=1',

                enabled               => TRUE,

                comments              => 'Every 1 hour sum amount of table1 and output to table2'





              Since above is just a simple demo of jobs with scheduler, so I am dropping that job:









              Is that you want ?



              Girish Sharma

              • 19. Re: Which is efficient?

                Of course, using a trigger will have a performance impact, but you have nothing at no cost.

                Ok, probably my last post here because you still did not even provided any table desc, nor effort to test anything.


                Here I go with a small and dirty test. Saying 1036800 is the number of rows you approximately have to insert per day:

                SQL> create or replace trigger trg_ai_mytable

                  2  after insert on mytable for each row

                  3  begin

                  4  insert into mytable_date(myid,mydate) values (:new.id,sysdate);

                  5  end;

                  6  /


                Trigger created.


                Elapsed: 00:00:00.03

                SQL> insert into mytable select rownum as rn, 'My string '||rownum as str from dual connect by level <=1036800;


                1036800 rows created.


                Elapsed: 00:02:09.53

                SQL> commit;


                129 seconds to insert 1036800 rows with a trigger

                You said, you have about 12 rows/sec, I inserted here 1036800/129=8037.2 rows/sec, means 669.7 times more than your requirement. Of course it depends of many factors.

                I do not say it's an ideal solution, but now your on your own to do your own conclusion/test/benchmark and so forth.



                • 20. Re: Which is efficient?

                  Thanks Girish,


                  But that is not what I wanted. The sample of yours will count everything over and over again.


                  This is it:


                  Supposing I have 24,000 rows inserted on EMP table for the whole day of 24 hrs.


                  1st hr > 1000

                  2nd hr > 1000

                  3rd hr > 1000

                  ...and so on


                  But in your program I will have duplicate counts like:


                  1st hr > 10000

                  2nd hr > 2000

                  3rd hr > 3000

                  ...so on

                  • 21. Re: Which is efficient?

                    Ok I will do benchmark and recommend to my boss if the result is positive. Thanks


                    Honestly, I am sad,  I missed sb and sybrand   and their comments. I used to it and it seems been part of my life

                    1 2 Previous Next