This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,699 Users
  • 2,269,776 Discussions


Fast table scan of ORA_ROWSCN

For change data capture (inserts and updates only) it would be useful to capture changes to a table by using ORA_ROWSCN. We would simply query the table where the ORA_ROWSCN was greater than the last SCN we used to query the table the last time we queried it. The problem is that this sort of query will perform a full table scan because ORA_ROWSCN is a pseudo column and cannot be indexed with a traditional index, so performance is slow on a multi-million row table.

The solution would be for the database to have some sort of fast scan capability on ORA_ROWSCN so it could efficiently find blocks/rows by SCN. I imagine some sort of feature like storage indexes on exadata might be a good implementation.

1 votes

Active · Last Updated