This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 18, 2013 5:20 AM by yxes2013 RSS

Which is efficient?

yxes2013 Newbie
Currently Being Moderated

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