select last rows

I just read about a query to retrieve last modification row of a date


SQL> SELECT ora_rowscn FROM tab_test;

ORA_ROWSCN
----------
351744
351744
351744
351744
351744
351744
6 rows selected.

SQL> UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1;

3 rows updated.

SQL> commit;

Commit complete

SQL> SELECT ora_rowscn FROM tab_test:

ORA_ROWSCN
----------

351744
351744
351744
371423
371423
371423

6 rows selected.


conclusion of the author : very neat to retrieve last modification date.

Well, I am worried. I have answered so many times on the technical forums here for example, that the only way to retrieve the last rows is to use a date column, and to manually update it (or with a trigger or a default value for insert) with the last modification date.

What should I do?
test it!


14:52:07 SQL> create table t as
select rownum x from all_objects;

Table created.

14:56:23 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 4238



let's update one row


14:54:12 SQL> update t set x=-1 where rownum=1;

1 row updated.

14:55:04 SQL> commit;

Commit complete.

14:58:03 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 3580
04.08.2006 14:57:14.00 658



what? I updated one row, why did it updated so many rows? Let's look at the block

14:58:16 SQL> select dbms_rowid.rowid_block_number(rowid) block_number,scn_to_timestamp(ora_rowscn) timestamp,count(*) from t group by dbms_rowid.rowid_block_number(rowid),scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

BLOCK_NUMBER TIMESTAMP COUNT(*)
------------ ---------------------- ----------
651 04.08.2006 14:56:23.00 658
652 04.08.2006 14:56:23.00 658
653 04.08.2006 14:56:23.00 658
654 04.08.2006 14:56:23.00 658
655 04.08.2006 14:56:23.00 658
656 04.08.2006 14:56:23.00 290
650 04.08.2006 14:57:14.00 658



ok, what has the doc to say about this :

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking


Well, let's try again with row dependencies.


15:04:53 SQL> drop table t;

Table dropped.

15:04:55 SQL> create table t rowdependencies as select rownum x from all_objects;

Table created.

15:05:28 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4241

15:05:42 SQL> update t set x=-1 where rownum=1;
1 row updated.

15:05:58 SQL> commit;

Commit complete.

15:06:09 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4240
04.08.2006 15:06:08.00 1


sounds better! probably not 100% reliable but way better, it seems almost usable ...