Sunday, October 16, 2005
Reinventing the wheel
I've been mulling over a few instances of wheel re-invention recently, the first was inspired by this entry over at Igor's lab. Igor has a neat way of displaying the time a record was committed. Unfortunately for me, it rather gets spoiled by the suggested application
Normally, it is difficult to pick records added or updated in a table since a particular time, as the delay between change and commit means that changes do not necessarily become visible in the order they are inserted. Even allowing, for example, a five minute window may miss long running transactions and requires processing to cater for duplicates.
Since 9i of course there is a much much easier way and one that doesn't require reinvention.
- records that have been added or updated since 17:00 on October 16th.
- and the more interesting from recovery points of view
There is a caveat to this though. If records have been updated in the time period of interest, you need to think carefully about why. Recall that the C in ACID stands for consistent. What else might have changed elsewhere in the database that prompted the update or deletion? Is it valid just to reverse the change or should we really be going back to 5pm for the whole database.
The second time I was thinking about wheel re-invention was in response to Scott Spendolini's piece on image manipulation here. It got me thinking of all the things that we are already supplied with that it is often tempting to re-invent. (I've not got as far as the Daily WTF and IHBLRIA) Image manipulation joins a list that includes
sequences
RI
Triggers - continuously poll for updates and take action when you see an update
read consistency - lock every table so that you can be sure the tables are consistent
I'm sure there are others.
0 Comments
Normally, it is difficult to pick records added or updated in a table since a particular time, as the delay between change and commit means that changes do not necessarily become visible in the order they are inserted. Even allowing, for example, a five minute window may miss long running transactions and requires processing to cater for duplicates.
Since 9i of course there is a much much easier way and one that doesn't require reinvention.
- records that have been added or updated since 17:00 on October 16th.
select * from t
minus
select * from t as of timestamp to_timestamp('16-OCT-05 17:00:00','DD-MON-YY HH24:MI:SS');
- and the more interesting from recovery points of view
select * from t as of timestamp to_timestamp('16-OCT-05 17:00:00','DD-MON-YY HH24:MI:SS');
minus
select * from t;
There is a caveat to this though. If records have been updated in the time period of interest, you need to think carefully about why. Recall that the C in ACID stands for consistent. What else might have changed elsewhere in the database that prompted the update or deletion? Is it valid just to reverse the change or should we really be going back to 5pm for the whole database.
The second time I was thinking about wheel re-invention was in response to Scott Spendolini's piece on image manipulation here. It got me thinking of all the things that we are already supplied with that it is often tempting to re-invent. (I've not got as far as the Daily WTF and IHBLRIA) Image manipulation joins a list that includes
I'm sure there are others.
0 Comments:
Post a Comment
