Re: "Re-sequencing" a pseudo-key
Date: Thu, 9 Oct 2014 12:28:09 +0200
Message-ID: <CA+S=qd30JypteH+wNafmsgPyzcCvnWgZBmO+DHosCWYFEn1Erg_at_mail.gmail.com>
As I see it, I think I would add new_recid columns for the pseudokeys and *also *new_refrecid columns for the foreign references.
Maybe on-insert triggers can be used to minimize the updates needed at cutover time, but if I can incrementally update the new_* columns and the translation table up to and including the night before cutover, only 1 days worth of new data would need updates at cutover. Might even be able to run the incremental update the evening just before cutover, then start the downtime window and run incremental update again - this time just on maybe an hour of new data (an hour at fairly low workload even.)
But the idea is very nice, and it is a great point that while it uses a lot of redo *in total*, that load is spread out thinly over a relatively long time so it does not hurt production but helps to minimize the cutover downtime.
Still details to work out, but I thank Iggy for giving me ideas to do this "over time" rather than necessarily "one big bang" (which I thought was needed ;-)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha
On Thu, Oct 9, 2014 at 11:32 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> love it.
>
>
>
> One possible optimization: If you can tolerate a small boundary hole in
> the new sequence number as a safety margin, forecast the new starting point
> leaving room for a dense mapping of the existing recids, add the new_recid
> column and begin populating them on insert from the new sequence number and
> post the pair to the translation table. I think you should be able to do
> the posting with a relevant on-insert trigger, since you cannot change the
> legacy code. Then only the foreign references will need to be updated at
> cutover time. Those foreign references seem to be the trickiest part.
>
>
>
> This method is by no means the least total redo or fastest for a linear
> shut down window. But that is not Iggy’s point: He has hit on the long
> calendar duration available to complete the change if most of it is done as
> a bit of trickle feed overhead with a moderate outage for the cutover at
> the end.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Iggy Fernandez
> *Sent:* Wednesday, October 08, 2014 7:51 PM
> *To:* kibeha_at_gmail.com; ORACLE-L
> *Subject:* RE: "Re-sequencing" a pseudo-key
>
>
>
> It's an amazing problem with numerous restrictions but where there is a
> will there may be a way.
>
>
>
> To summarize, you have a list of sequence number with holes. The holes are
> probably caused by the way the sequence numbers are being used. A process
> that reads 10 sequence numbers may end up only using a few of them. This
> results in the sequence numbers being gobbled up faster than expected.
>
>
>
> The challenge is doing billions and billions of lookups into the
> translation table in a short downtime window without an Exadata. Winning
> the lottery or finding that much spare change is unrealistic. Not to
> mention that you have to update indexes and refresh materialized views.
>
>
>
> I think an incremental solution is possible as follows:
>
>
>
> 1. Add new_recid columns to each table. There will be as many new
> columns in each tables as there are columns that need to be translated. For
> now, they need to be nullable.
> 2. Create a master translation table with 600 million rows and two
> columns: recid and new_recid. New rows are being constantly added to the
> database but that's not a problem for this algorithm. New rows can be
> tackled later. Using a master translation table for all recid columns will
> ensure that all the translations are in the same order as the original
> values. Preserving this order is not necessary but is aesthetically
> pleasing.
> 3. Incrementally update the values of the new_recid column with the
> translation of the corresponding recid value. This can be done
> incrementally over the next six months. If updating a large number of rows,
> you can commit at regular intervals to avoid long transactions.
> 4. Purge the master translation table and repeat steps 2 and 3 for new
> entries only. Keep doing this until the day of cutover. In fact, you don't
> have to tackle 600 million entries in the first shot. You could do perhaps
> 10 million entries every day for 60 days.
> 5. As you get close to the cutover date, you can begin building
> indexes involving the new_recid columns instead of the recid columns using
> ONLINE build. You can also create a second set of MVs using the new_recid
> columns. For fast refreshability you can use ROWID materialized views
> instead of PRIMARY KEY materialized views. The only downside is that any
> DDL operation that requires row movement will force a complete refresh but
> what are the chances.
>
>
>
> With this incremental approach, you have not impacted the application in
> any way up to the time of cutover. The cutover steps:
>
>
>
> 1. Perform the last few updates.
> 2. Put the database into flashback recovery mode.
> 3. Drop primary key and foreign key constraints
> 4. Rename the recid columns to old_recid and new_recid columns to recid
> 5. The required indexes already exist
> 6. The new materialized views already exist. You don't need to rename
> them if you are using automatic query rewrite. If you are accessing them
> directly, you will have to work out some solution involving prebuilt tables.
> 7. You may not need the primary key constraints any more
> 8. You can drop the old_recid columns; it would happen instantaneously
> but you can postpone this step to a future time
> 9. Test the application and flash back if there is a failure
>
>
>
> The downside to an approach such as this is that you have added new
> columns and caused some chained rows and row migrations. Lots of details
> still need to be worked out. Lots of testing will ensure that you have a
> good plan.
>
>
>
> Now if you had an Exadata ...
>
>
> ------------------------------
>
> Date: Wed, 8 Oct 2014 12:08:24 +0200
> Subject: "Re-sequencing" a pseudo-key
> From: kibeha_at_gmail.com
> To: oracle-l_at_freelists.org
>
> Hi, list
>
>
>
> The short description of my question is:
>
>
> · I’ve got 600 million rows in 650 tables and need to update all
> rows to “re-sequence” a 32-bit integer pseudo key in all tables. How to
> approach this update task with reasonable efficiency?
>
> I can’t think of a way to describe the task very briefly without getting a
> lot of answers where I’ll have to say “well, there’s just this little
> special issue that makes your good idea unable to work here”, so this’ll be
> a longish mail. If you haven’t got the time or inclination to read through
> this bunch of details, please feel free to stop reading now :-)
>
>
>
> For those reading on, I’ll try to describe the setup and the challenge in
> a bunch of points. That way you can refer to “point 5.c.2 is wrong, young
> man” or something like that ;-)
>
>
>
>
> 1. Total about 650 tables totaling about 600 million rows
>
> a. 1 table about 300 million rows
>
> b. 7 tables each in 10-40 million rows range totaling about 100
> million rows
>
> c. 37 tables each in 1-9 million rows range totaling about 100
> million rows
>
> d. The rest 600+ tables each less than 1 million rows totaling about
> 100 million rows
>
>
> 2. Each table has numeric pseudokey called RECID
>
> a. Legacy application code mandates this to be 32-bit integer
> -2,147,483,648 to 2,147,483,647
>
> b. Populated by one common sequence created with INCREMENT BY 10
>
> c. Legacy kernel (unchangeable) code works like:
>
> i. When
> needing to insert a row in any table, it selects NEXTVAL FROM DUAL
>
> ii. It
> inserts row using the selected sequence value
>
> iii. The
> next 9 inserts from this session to any table will use the next 9 values
>
> iv. The 10
> th insert will again select NEXTVAL FROM DUAL
>
> d. Sequence originally started at 1 going up towards 2,147,483,647
>
> e. Few years ago max was reached and sequence reset to start at
> -2,147,483,648
>
> f. We are now at about -340,000,000 and will reach zero late 2015
> or early 2016
>
> g. “Re-sequencing” pseudokeys will “use” 600 million of the 4200
> million
>
> h. Then we would have 3600 million available or about 10-12 years
> before next problem
>
> i. Time to research solution is now
>
> i. If
> this cannot be done in one night, Easter 2015 is the only big window
>
>
> 3 Key constraints are only partially defined in Oracle
>
> a. Legacy application treats Oracle as black box and handles keys
> in application
>
> b. So no primary or foreign keys in Oracle – only defined in
> application metadata
>
> c. Except some tables are replicated with materialized views
>
> i. These
> tables have materialized view logs
>
> ii. Therefore
> they have primary key in Oracle defined as RECID column
>
> d. Many places in application have “multi-parent foreign key”
>
> i. One
> column designates which parent table
>
> ii. Other
> column contains parent RECID
>
> e. Some “generic” tables exist in the application
>
> i. For
> example if COL2=17, then COL5 contains parent RECID of one table
>
> ii. If
> COL2=18, then COL7 contains parent RECID of another table
>
>
> 4. Legacy application is index happy
>
> a. Average almost 4 indexes per table
>
> b. Up to 22 indexes on a few tables
>
> c. The one big table of 300 million rows has 16 indexes
>
> d. Every table has a unique index on RECID
>
> e. Indexes are all created as UNIQUE
>
> i. Application
> makes non-unique indexes UNIQUE by adding RECID as last element
>
> ii. If
> it is truly unique in the application, RECID is not in the index
>
>
> 5. IDEA 1 – DML
>
> a. Massive DML for each table
>
> i. CREATE
> TABLE tab1$newrecid AS
> SELECT recid as oldrecid, rownum as newrecid
> FROM tab1
> ORDER BY recid
>
> ii. UPDATE
> tab1 SET recid = newrecid
>
> iii. UPDATE
> tables with FKs to tab1 SET parentrecid = newrecid
>
> b. Pros
>
> i. Simple
> and easy
>
> c. Cons
>
> i. Slow
> DML of all rows
>
> ii. Index
> maintenance
>
> iii.
> Replication
>
> 1. All rows go in the mview logs
>
> 2. FAST REFRESH will be very slow based on the bloated mview logs
>
>
> 6. IDEA 2 – DDL
>
> a. A lot of CTAS
>
> i. For
> all tables create translate table
> CREATE TABLE tab1$newrecid AS
> SELECT recid as oldrecid, rownum as newrecid
> FROM tab1
> ORDER BY recid
>
> ii. For
> each table do CTAS joining PK and any FKs to translate tables
>
> 1. CREATE TABLE tab1$copy AS
> SELECT t2.newrecid as recid,
> t1.othercolumns,
> t15.newrecid as t15parentrecid,
> t17…
> FROM tab1 t1
> JOIN tab1$newrecid t2 ON t2.oldrecid = t1.recid
> JOIN tab15$newrecid t15 ON t15.oldrecid = t1.t15parentrecid
> JOIN tab17$newrecid …
>
> 2. DROP tab1
>
> 3. RENAME tab1$copy TO tab1
>
> 4. Rebuild indexes
>
> 5. Create mview log if table is replicated
>
> 6. Recreate / refresh complete replicated mview on target database
>
> b. Pros
>
> i. Probably
> a good deal faster than DML
>
> c. Cons
>
> i. Extra
> storage needed
>
> ii. Extra
> work rebuilding indexes
>
> iii. Extra
> work rebuilding replication
>
> iv. More
> complex code to write
>
> v. Downtime
> on replication target database
>
>
> 7. Some of my thoughts
>
> a. Normally I can have a night service window maybe 3 hours
>
> b. For this I can arrange up to 10-12 hours if need be
>
> c. If that won’t be sufficient, Easter can provide 60 hour window
>
> d. My priority is “safety” – as little chance of something going
> wrong as possible
>
> e. I can arrange the storage needed for the copy tables of the DDL
> method
>
> f. I think the DDL method is most likely “best”, but can it be
> “finetuned”?
>
> g. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -
> 64bit Production
>
> h. Running on Oracle Linux Server x86_64 release 6.5 with 4 cores
> and 512 GB memory
>
> i. I can test this full scale on a dataguard copy that I can put
> in snapshot standby mode
>
>
> 8. My questions
>
> a. Have I missed something obvious?
>
> b. Ideas for a different method?
>
> c. Ideas for improving some of the steps in my ideas?
>
> i. Would
> it help if the “translate” tables were IOTs?
>
> ii. Or
> give the “translate” tables a tablespace with bigger blocks?
>
> d. Things to consider that can give considerable speed improvements?
>
> i. (I
> mean, adding complexity to gain a few minutes isn’t interesting ;-)
>
> e. Any database parameters that should be increased while doing this?
>
> f. Parallelism? DIY with 4 sessions each handling a different set
> of tables?
>
>
>
> So, to any that got this far – thank you for your patience in reading this
> :-)
>
>
>
> I hope I can either get confirmation that my ideas seem reasonably OK or
> some ideas for improvement I can try out.
>
>
>
> Thanks in advance.
>
>
>
>
>
>
>
> Regards
>
>
>
>
>
> Kim Berg Hansen
>
>
>
> http://dspsd.blogspot.com
>
> kibeha_at_gmail.com
>
> _at_kibeha
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 09 2014 - 12:28:09 CEST