Re: Question on IOT with ascending primary key with a twist
Date: Fri, 17 Jan 2014 09:25:05 +0100
Message-ID: <CA+S=qd3v4gMuD+QN69p050ezS=v2=+V+ga4dbTKkALFDPFwAJg_at_mail.gmail.com>
Well, as I mentioned this system is its own development environment - that includes it has its own data dictionary where it handles referential integrity. The developers 20 years ago decided to treat the underlying Oracle as a black box of just tables, columns and indexes - no referential integrity constraints declared in the database, just in the "application data dictionary" :-(
That's bad for me in the sense that the database can't help me discover if I've missed anything. But maybe "good" in the sense that I can do massive DML without worrying about database error on bad foreign keys.
One of the things this application does a lot is "multi-parent foreign keys". Like for example a table containing Notes has columns RefFileId and RefRecId. If RefFileId is 11, then the parent of "FK" RefRecId is in column RecId (this 32-bit pseudokey I need to re-sequence) in table Products. If RefFileId is 43, then the parent is in table Customers. And so on.
Then there are also a few "generic tables" where "if type=7" then column Int4 contains a RecId from parent table Orders. This is not well documented, I need "almost" to look at every bit of 20 years code to discover this :-(
So far I think my plan is something like:
- Create an IOT with FileId, OldRecId, NewRecId (or maybe better yet an IOT for each table with just OldRecId, NewRecId).
- For each table create an empty copy of the table
- Multitable insert into the copy table and the OldNew table with something like:
INSERT /*+ append */ ALL
INTO Tab1Copy (RecId, Col1, Col2, ...) Values (NewRecId, Col1, Col2, ...)
INTO OldNew (OldRecId, NewRecId) Values (OldRecId, NewRecId)
SELECT RecId as OldRecId, ROWNUM as NewRecId, Col1, Col2, ...
FROM Tab1
ORDER BY RecId
- "Update" "FK" columns by lookup in OldNew (probably via CTAS into yet another copy)
- Drop old tables, Rename copy tables
- Build indexes
- If I missed some FK, then I still have the OldNew table available to change missed RecIds from OldRecId to NewRecId
(And then after all that I'll completely rebuild the matview replicated tables on the other database.)
Yes, I can test this in full scale. I have a dataguard copy that we set in snapshot standby mode just for things like this :-)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha
On Thu, Jan 16, 2014 at 9:46 PM, Powell, Mark <mark.powell2_at_hp.com> wrote:
> Yikes. I have done the re-sequencing of keys task myself. I inserted
> the existing row with a new key then updated all dependent children to have
> the new key followed by the delete of the original parent all as a single
> transaction. That is, I performed the parent and child changes within the
> same transaction and committed after every parent before proceeding to the
> next set of rows. In my case every child table had a defined FK constraint
> to the parent, but not every FK type relationship is always defined with
> declared constraints especially if the data is not properly normalized.
> This can make being sure you performed all necessary updates an issue. I
> hope you can perform the task in test and have users check out the
> functionality before you have to do this to production. I seem to
> remember the indexes grew a fair amount during the processing. I know I
> rebuilt all of them when I was done.
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 17 2014 - 09:25:05 CET