How to improve a SCN-based query performance?

Question

In Oracle database there is a pseudocolumn which's called ora_rowscn. If it's retrieved it shows the SCN of the most recent change to the row (as it's said in the documentation).

Also there's an option rowdependencies of CREATE TABLE which switches on storage of SCN for each row instead of a whole data block (which is default).

So, I'm using values of this column for indicating which rows were updated and are needed to be uploaded to another database.

Let's consider this example:

1. There's a table T1 in schema S1 which contains several millions of records (full scan on the table is not affordable for regular queries).

   CREATE TABLE T1 {
     A INTEGER PRIMARY KEY,
     B VARCHAR2(100),
     C DATE
   }
   /
   

2. There're schemas S2, S3, S4, S5.. and in each of them there's table T2.

   CREATE TABLE T2 {
     A INTEGER
   }
   /
   

3. There's only one row in T2, but value of T2.A can be different in different schemas.

So, I need to retrieve in each schema (S2, S3, S4...) all rows from S1.T1 which have value of ora_rowscn greater than S*.T2.A (then I use this data block). After gettting these rows I rewrite value of S*.T2.A with the current system SCN (dbms_flashback.get_system_change_number).

The following queries for any schema is right here:

Query 1:

SELECT * FROM S1.T1 WHERE ora_rowscn > (SELECT A FROM T2);

Query 2 (it's performed when I finished work with dataset returned by the previous query):

UPDATE T2 SET A = dbms_flashback.get_system_change_number;

The problem is that performance of query 1 is unacceptable (full scan on the table S1.T1) and the column ora_rowscn can't be indexed.

The question: What could be the ways to improve the performance of the query 1?

Answer 1

You can't index ora_rowscn. Consequently the best plan for query 1 is a FULL TABLE SCAN.

Since this is not acceptable, you will have to use another marker, for example a last_updated date column. This column is indexable but you'll have to update it. You can automatize this update with a small light-weight trigger.

Performance of query 1 against an indexed column will be dependent upon the number of rows retrieved.