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

Rune's gravatar image

accept rate: 50%

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.

permanent link

answered 26 Sep '11, 08:53

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

The docs doesn't explicitly say that last_modified_tsn is only updated when a checkpoint occurs. (but it would make sense)

Is it the same type of checkpoint that are mentioned in the log: "Starting checkpoint of "mm3tom" (mikromarc.db) at Tue Sep 27 2011 13:54" ?

I need a bit more accuracy than once every hour. Adding an entire timestamp column just to get at a single value seems as a bit of an overkill.

Thanks for the help though.

(27 Sep '11, 08:30) Rune
Replies hidden

Ths page (SYSTAB table) in the docs that I refered to mentions that the column is only updated at a checkpoint: "last_modified_at - TIMESTAMP - The time at which the data in the table was last modified. This column is only updated at checkpoint time.".

Re. "Is it the same type of checkpoint that are mentioned in the log ...": Yes.

Depending on how often you want to get the last modified value you could choose to do an explicit checkpoint (by issuing the "CHECKPOINT" statement)... but I would not do this often since (manual) checkpoints are expensive.

(27 Sep '11, 08:39) Mark Culp

@Mark: The docs don't state that the "last_modified_tsn" column is only updated on checkpoints as well - is it?

(27 Sep '11, 09:44) Volker Barth

You can explicitly influence the maximum time between checkpoint intervals by setting the dbsrv12 -gc server option - just in case an hour is too long but a shorter frequency like 10 minutes might be affordable (both for accuracy and database performance...)

(27 Sep '11, 09:48) Volker Barth

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...

AFAIK, there's only the creation time for all database objects available, cf. the SYSOBJECT system view. But that does only refer to the creation of the table itself, not its data.

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).

permanent link

answered 26 Sep '11, 08:44

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 26 Sep '11, 09:02

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 26 Sep '11, 08:34

question was seen: 5,624 times

last updated: 27 Sep '11, 09:48