Request last modified time without last modified column

Question

I am using Oracle 10g XE. I have a database query -

SELECT PID FROM PROUCT WHERE last_modified > '${dih.last_index_time}' 

What I'm trying to do is compare timestamps. The problem is that there is no last_modified column in my table and I cannot add it to the table. However, there is a request -

 SELECT SCN_TO_TIMESTAMP( ORA_ROWSCN ) FROM PRODUCT; 

This will return a column that will contain the required timestamps.

How to use this query instead of the last-modified column to make the necessary comparison?

Answer 1

You just put it in the WHERE clause:

 SELECT PID FROM PRODUCT WHERE SCN_TO_TIMESTAMP( ORA_ROWSCN ) > '${dih.last_index_time}' 

However , your two queries are not equivalent. From ORA_ROWSCN (my emphasis):

For each row, ORA_ROWSCN returns the conservative top system change number (SCN) of the last change in the row in the current session. This pseudo-column is useful for determining approximately when the last row was updated. This is not entirely accurate , because Oracle tracks the SCN with a transaction committed to the block in which the row is located.

This means that if you change one line in a block, the entire block (several lines) will have the same SCN.

If you want your query to be accurate, you need to either add your LAST_MODIFIED column, or perhaps enable row-level dependency tracking . This is still inaccurate:

... each row in the table has a system change number (SCN), which represents a time greater than or equal to the commit time of the last transaction that changed the row