RE: "Re-sequencing" a pseudo-key (another solution)
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "kibeha@xxxxxxxxx" <kibeha@xxxxxxxxx>
- Date: Thu, 16 Oct 2014 12:45:04 -0700
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@xxxxxxxxxxx
To: kibeha@xxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
