RE: "Re-sequencing" a pseudo-key (another solution)
Date: Thu, 16 Oct 2014 12:45:04 -0700
Message-ID: <BLU179-W95F3F64E279A3C9F0E18ADEBAB0_at_phx.gbl>
re: update an inline view formed by restricting the table to RECIDs -97 and -96 and joining with the translation table I meant to say update an inline view formed by restricting the table to RECIDs -97 and -96 and joining the table to its corresponding translation table (only one join required).
From: iggy_fernandez_at_hotmail.com
To: kibeha_at_gmail.com
CC: oracle-l_at_freelists.org
Subject: RE: "Re-sequencing" a pseudo-key (another solution)
Date: Thu, 16 Oct 2014 12:27:28 -0700
Hi, Kim,
I have another solution for you. I'll explain it with an example consisting of 10 rows. Suppose that the list of RECIDs (union of all 300 tables) is as follows: +1, +2, +3, +4, +100, -100, -99, -98, -97, -96. In this example, the MAXVALUE was 100. Wraparound occurred at 100 and the values are creeping back to 1. We have to take action before the values reach 1.
The only assumption that is needed for the following solution to work is that the application will not change RECIDs while the resequencing is in progress:
Step 1Identify the last RECID value that is currently in use (the answer is -96). We will update RECIDs to this point, fully aware that new RECIDs are being added all the time.Count the number of RECIDs upto -96 (the answer is 10)Therefore we will map the RECIDs to the following list: +96, +97, +98, +99, +100, -100, -99, -98, -97, -96.Create a master translation table with the following tuples:(1, -96, -96)(2, -97, -97)(3, -98, -98)(4, -99, -99)(5, -100, -100)(6, +100, +100)(7, +4, +99)(8, +3, +98)(9, +2, +97)(10, +1, +96)The first column is a permanent row number for convenience. Notice that the latest RECID has the smallest row number; that is, the row numbers are assigned in the opposite order of when they were created in the database. The reason will become clear in Step 3.Step 2Prepare separate translation tables for each of the 300 tables in order avoid the run-time expense of master translation table lookups.The columns of each translation table are as follows: (PK_RECID, PK_TRANSLATION, FK1_TRANSLATION, FK2_TRANSLATION, ...)If you like, the translation tables could all be external tables to avoid creating redo. Ditto for the master translation table in Step 1.Step 3Divide the work into manageable subsets. For this explanation, let's do only two RECIDs at a time.The trick is to work backwards in time to avoid RECID collisions; that is, to avoid the possibility that a translated RECID conflicts with an existing RECID. That's why we assigned row numbers in the opposite order in which RECIDs were created. Note that we are working backwards in time not by mathematical value. Therefore we process the tuple (1, -96, -96) first and the tuple (2, -97, -97) second.Write a transaction as follows:begin defer constraints on all 300 tables; for each table update an inline view formed by restricting the table to RECIDs -97 and -96 and joining with the translation table; end; commit;end;Working backwards in time, process additional chunks of RECIDs, until you are done.
Will this work?
Iggy
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 16 2014 - 21:45:04 CEST