RE: "Re-sequencing" a pseudo-key

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Wed, 8 Oct 2014 16:50:33 -0700
Message-ID: <BLU179-W218584B5254AE3E4E7670EBA30_at_phx.gbl>

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: 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.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.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.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.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: Perform the last few updates.Put the database into flashback recovery mode.Drop primary key and foreign key constraintsRename the recid columns to old_recid and new_recid columns to recidThe required indexes already existThe 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.You may not need the primary key constraints any moreYou can drop the old_recid columns; it would happen instantaneously but you can postpone this step to a future timeTest 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

1. 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
3. 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 10th 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

1. 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
2. Legacy application is index happy
3. 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
4. IDEA 1 – DML
5. 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
7. 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
8. Some of my thoughts
9. 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
10. My questions
11. 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.comkibeha_at_gmail.com _at_kibeha                                                

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 09 2014 - 01:50:33 CEST