RE: data migration

From: Morton, Ronald D <rdmorton_at_switch.com>
Date: Fri, 18 May 2001 04:09:24 -0700
Message-ID: <F001.00307535.20010518040029@fatcity.com>

If the remaining 90% of data is not going to change while you test-run the first 10%, then how about something like this:

run this against the original data just before moving the 10%: SELECT 'CREATE SEQUENCE '||sequence_name||' START WITH '||last_number + 1||';' FROM user_sequences;
This gives you a set of numbers that are above your most recent data and will not interfere with your PK constraint.

After moving the 10% to the new DB, drop all sequences in the new DB and run the generated script from above.

Just my .02 worth.

Ron

-----Original Message-----
Sent: Thursday, May 17, 2001 5:46 PM
To: Multiple recipients of list ORACLE-L

Hi,

We are planning to do two-phase data migration - split by data. In first phase, about 10% of the data will be moved as is - without changing the primary keys (generated from sequences). Once we start using the database with phase 1 data, sequences will generate new primary keys and add to this data - which could be same as primary key for the remaining data, which is not yet migrated. In phase 2, to avoid unique constraint problems, we'll add 1,000,000 to primary keys before we migrate remaining data. This will lead to imbalance of primary keys. After complete migration, we'll have about 10% of the keys with low values and remaining with very high values. What is the impact of this huge gap in primary keys? How will be the performance affected? Is there a way in which we can avoid this huge gap in primary keys (We do not have an option of going for single migration. Business needs force us to test the application with partial data. ) One option I thought of is to use high value keys in first migration itself - but when sequences reach these high values, we'll have same problems of unique constraint being violated.

Thanks.

Praful

Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Praful Thakkar
INET: thakkar_praful_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Morton, Ronald D
INET: rdmorton_at_switch.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------