1 2 Previous Next 21 Replies Latest reply: Oct 18, 2013 7:20 AM by yxes2013 RSS

    Which is efficient?

    yxes2013

      Hi all,

       

      11.2.0.1

       

      I have a table PAYMENT with column AMOUNT and with around 5M rows. This table is reset to 0 every midnight and get inserts 12 rows per second.

      I want to SUM(AMOUNT)  the table every how and print report, so that I have an hourly AMOUNT TOTAL.

      My problem is I do not have unique sequence numbering of the table, nor timestamp of it. I can not alter this table since it belongs to a 3rd party software app, which may cause issue to the app itself if I alter it.

      My recourse is to create a trigger that inserts to another LOG table a unique sequence_number and AMOUNT for each row, whenever an insert is done to the source table.

       

      My question is which is the efficient way of creating a LOG table? is it a trigger or using materialized view log?

       

       

      Thanks a lot,

      zxy

        1 2 Previous Next