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
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.