Re: Auto sequencing

Shaun,
Moving data to another instance when sequence generated numbers are used is always a potential problem. I worked with a DBA who had been at a bank where they merged many instances together (multiple bank branches). He taught me that one should think ahead and prepare a value for the key field which is notjust numeric, but also incorporates some other value which might be location or instance specific, such as branch or city or instance. Then, when life changes and eventually someone decides to merge data, there won't be a conflict with sequence numbers: even if the value of the number is the same, the other part of the key will be different.

Based on this knowledge, I prepared Varchar2 ID columns and Stored Functions which called the sequence generator for me. In my case, I used two identifiers: plant type, and ID Type to create the uniqueness. Thus, a Nextval of 123456 became "Long Beach" + "UserID" + NNNNNN

      LBUI123456. By the way, large companies have these problems too: True Story: When I lived in Long Beach, and owned a cell phone...one day, in the mail, I received a cell phone I had not ordered. I noticed that it had a San Diego area code (200 miles south of Long Beach). When I called the shipping center and spoke to the tech, I said, "gee, it sounds just like two Oracle databases were merged which had duplicate sequence numbers" He told me that it was exactly the case, and that he had sort of been expecting some kind of problem. Two shipping divisions in Southern California had been merged into one and the shipping software, when presented with a San Diego customer number found the first available record, which was my Long Beach customer number.

Another approach, which was used here at my current company involved migrating current data and then controlling future data. The current data was migrated to the new database intact, with sequence numbers as is. Then, the sequence generators were set forward by a large enough gap to ensure that all new data would never interfere with any previous data. In our case, they jumped about 10- to 20,000. It was not important whether sequence numbers be consecutive. While I feel that it was an appropriate one time measure, they are only postponing trouble should they have to migrate again in the future...

Good Luck
Robert Proffitt
RTProffitt_at_Beckman.com
Beckman Coulter
Brea, California

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 18 2000 - 00:00:00 CDT