oracle - How to improve a SCN-based query performance? -

in oracle database there pseudocolumn which's called ora_rowscn. if it's retrieved shows scn of recent change row (as it's said in documentation).

also there's option rowdependencies of create table switches on storage of scn each row instead of whole data block (which default).

so, i'm using values of column indicating rows updated , needed uploaded database.

let's consider example:

1. there's table t1 in schema s1 contains several millions of records (full scan on table not affordable regular queries).

   create table t1 {   integer primary key,   b varchar2(100),   c date } / 

2. there're schemas s2, s3, s4, s5.. , in each of them there's table t2.

   create table t2 {   integer } / 

3. there's 1 row in t2, value of t2.a can different in different schemas.

so, need retrieve in each schema (s2, s3, s4...) rows s1.t1 have value of ora_rowscn greater s*.t2.a (then use data block). after gettting these rows rewrite value of s*.t2.a current system scn (dbms_flashback.get_system_change_number).

the following queries schema right here:

query 1:

select * s1.t1 ora_rowscn > (select t2); 

query 2 (it's performed when finished work dataset returned previous query):

update t2 set = dbms_flashback.get_system_change_number; 

the problem performance of query 1 unacceptable (full scan on table s1.t1) , column ora_rowscn can't indexed.

the question: what ways improve performance of query 1?

you can't index ora_rowscn. consequently best plan query 1 full table scan.

since not acceptable, have use marker, example last_updated date column. column indexable you'll have update it. can automatize update small light-weight trigger.

performance of query 1 against indexed column dependent upon number of rows retrieved.