Vinay Chandupatla, February 08, 2008 - 9:55 am UTC
I was just wondering if there was a way Oracle Stores the timestamp for each row as psuedo column (like rowid ). I was trying to avoid creating new tables.
Anyway I am going use the technique you have suggested.
The following table will also have and amount field. I will also have to do a sum(amount) each day. I think I can use the same technique to achieve the results.
insert into my_table
select trunc(sysdate-1) dt, count(*) cnt,sum(amount) amt
from that_table;
The above query runs as a job everyday at 6am
select dt, cnt - lag(cnt) over (order by dt) last_cnt rows_added, amt-lag(amt) over(order by dt)
from my_table;
The above queriy gives the number of rows added and their total of the amounts.
Let me know if the above works,
Thank you,
VInay
February 08, 2008 - 3:16 pm UTC
There is this thing called the ora_rowscn (search for it on this site)
You'd have to rebuild the tables to enable it at the row level (works at the block level)
and it would only go back for about 5 days using the scn to timestamp function (the scn would go back forever, but you'd have to remember the scn's for each day - what the ending scn was)
yes, the lag query would work
Oracle CDC
illiyaz, February 09, 2008 - 10:32 am UTC
Vinay,I am not sure if you have heard of Oracle CDC.It is pretty kool feature and not that hard to set up.Solves all the incremental load headaches and is pretty quick too.