Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Matthias.

Asked: July 21, 2021 - 8:11 am UTC

Last updated: July 22, 2021 - 10:19 am UTC

Version: Oracle Database 19c

Viewed 100+ times

You Asked

Dear TOM!

Is it save to use Flashback Query with views?

Setup:
CREATE VIEW my_view AS
SELECT a.*, b.* 
  FROM table_a a
  JOIN table_b b
    ON (a.ID = b.ID_A);


Flashback Query at view level:
SELECT v.*
  FROM my_view AS OF TIMESTAMP systimestamp - INTERVAL '30' SECOND v;  


Flashback Query at table level:
SELECT a.*, b.* 
  FROM table_a AS OF TIMESTAMP systimestamp - INTERVAL '30' SECONDa
  JOIN table_b AS OF TIMESTAMP systimestamp - INTERVAL '30' SECONDb
    ON (a.ID = b.ID_A);


Will both queries reliably deliver the same result?
Will the result of both queries be consistent over all affected tables?

Thank you,
Matthias

and we said...

Yes, you can use point in time (as of) flashback query on views:

create table t1 (
  c1 int
);
create table t2 (
  c1 int
);

create or replace view vw as 
  select * from t1
  join   t2
  using  ( c1 );

insert into t1 values ( 1 );
insert into t2 values ( 1 );
commit;

exec dbms_session.sleep ( 10 );

insert into t1 values ( 2 );
insert into t2 values ( 2 );
commit;

exec dbms_session.sleep ( 10 );

insert into t1 values ( 3 );
insert into t2 values ( 3 );
commit;

select * from vw
  as of timestamp sysdate - interval '15' second;
  
C1   
    1
  
select * from vw
  as of timestamp sysdate - interval '5' second;
  
C1   
  1 
  2 
  
select * from vw;

C1   
    1 
    2 
    3 


Though the versions between syntax is unsupported:

select * from vw
  versions between timestamp minvalue and maxvalue;  

ORA-30051: VERSIONS clause not allowed here


To verify what's going on here, you can pass your flashback query to dbms_utility.expand_sql_text. This shows you the full statement the database executes.

Do this and you'll see that the as of clause is pushed down to each table, ensuring you get the values from each table at that time:

declare
  l_clob clob;
begin
  dbms_utility.expand_sql_text (
    input_sql_text  => q'!select * from vw
  as of timestamp sysdate - interval '25' second!',
    output_sql_text => l_clob  );
  dbms_output.put_line(l_clob);
end;
/

select "A1"."C1" "C1"
from (
  select "A2"."C1_0" "C1"
  from (
    select "A3"."C1" "C1_0"
    from "CHRIS"."T1" as of timestamp (sysdate - interval '+00 00:00:25.000000' day (2) to second (6)) "A4",
         "CHRIS"."T2" as of timestamp (sysdate - interval '+00 00:00:25.000000' day (2) to second (6)) "A3"
    where "A4"."C1" = "A3"."C1"
  ) as of timestamp (sysdate - interval '+00 00:00:25.000000' day (2) to second (6)) "A2"
) as of timestamp (sysdate - interval '+00 00:00:25.000000' day (2) to second (6)) "A1"


So yes, your two queries are equivalent.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.