So NOLOGGING might not be a good idea.
There will be new rows getting created in the source table though.
Since other activity is taking place at the same time, Tamil's solution has the advantage of keeping the database always in a consistant state - you'll never find a row in two places.
Last edited by DaPi; 02-07-2006 at 06:20 PM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
That would depend on whether the new activity includes inserting or updating rows so that they fall into the timestamp period to be archived -- if not then having the same row in two places at the same time seems to be a small price to pay -- it's pretty (IMHO) rare that a query would address both archived and non-archived rows such that a double-count might be an issue.
You can use "where current of" to delete locked rows.
Tamil / Ken : How do I delete just the locked rows ? And, if I may know, why do you think the rownum based solution will not work ? Any technical reason for that ?
The reason for locking is no one can change/update/delete the rows that you want to insert into archived table.
You can use rownum, however you must use "FOR UPDATE" along with "SELECT" statement.
For performance reason NOLOGGING, APPEND, multiple threades etc can be used, but they will not solve basic logic.
Click Here to Expand Forum to Full Width