I have a table containing millions of rows. I need to know when that table was last modified.
We have tried using a trigger which updates an entry in a separate table, but some of our transactions take time and the resulting deadlocks killed us.
I thought I had read somewhere that SQL Anywhere 11 introduced a mechanism for reading a "last modified" timestamp for a given table, but I have googled my little eyes out and found nothing.
(We are using SQL Anywhere 12 now though)
Any elegant solutions? Or do I need to put a timestamp on every row?
asked 26 Sep '11, 08:34
You could use the last_modified_at column in the SYSTAB table or if you don't actually care about knowing the actual time use the last_modified_tsn column. The drawback is that these values only get updated (in the table) when a checkpoint occurs.
Alternatively you could add a "last_modified timestamp default timestamp" to your table, index the column, and then do a "select top 1 last_modified from T order by last_modified desc" to get the time of the last modified row.
answered 26 Sep '11, 08:53
Are you interested in schema or data modifications on that table?
EDIT: Based on Mark's response, I correct my statement - I totally missed those SYSTAB columns...
A common approach to monitor changed data would be to add a column with "DEFAULT TIMESTAMP" to your table. Columns with this special value get updated automatically if you insert/update the according row (unless you set the value explicitly when inserting/updating).