Flashback Query?
Posted: Fri Mar 23, 2012 8:56 pm
Hi, In Oracle I can do Oracle flashback queries where I can query data as it was as of a certain time stamp or scn (system change number). Is this possible in Vertica?
There is a similar feature in Vertica implemented as Historical (Snapshot) Queries.
There are two options:
- Query all committed data in the database up to, but not including, the current epoch
Query all committed data in the database up to the time stamp specified. AT TIME 'timestamp' queries are resolved to the next epoch boundary before being evaluated.
First I'll create a test table:
Code: Select all
dbadmin=> create table hist_test (col1 int, col2 varchar(100)) order by col1;
CREATE TABLE
dbadmin=> insert into hist_test values (1, 'Value #1');
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMITCode: Select all
dbadmin=> select current_epoch, ahm_epoch, last_good_epoch from system;
current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
299261 | 299258 | 299259
(1 row)
Code: Select all
dbadmin=> insert into hist_test values (2, 'Value #2');
OUTPUT
--------
1
(1 row)
Code: Select all
dbadmin=> select * from hist_test;
col1 | col2
------+----------
1 | Value #1
2 | Value #2
(2 rows)
Code: Select all
dbadmin=> at epoch 299259 select * from hist_test;
col1 | col2
------+------
(0 rows)
dbadmin=> at epoch 299260 select * from hist_test;
col1 | col2
------+----------
1 | Value #1
(1 row)
dbadmin=> at epoch 299261 select * from hist_test;
ERROR: Epoch number out of range
HINT: Epochs prior to [299259] do not exist. Epochs [299261] and later have not yet closed.
dbadmin=> commit;
COMMIT
dbadmin=> at epoch 299261 select * from hist_test;
col1 | col2
------+----------
1 | Value #1
2 | Value #2
(2 rows)