RE: Question on IOT with ascending primary key with a twist
Date: Thu, 16 Jan 2014 20:46:08 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1187C814D_at_G6W2491.americas.hpqcorp.net>
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.
From: Kim Berg Hansen [mailto:kibeha_at_gmail.com]
Sent: Thursday, January 16, 2014 3:04 PM
To: Powell, Mark
Cc: oracle-l_at_freelists.org
Subject: Re: Question on IOT with ascending primary key with a twist
Interesting idea, Mark
Problem is that the application is an old legacy ERP system that has its own programming language, own development environment, and own forms/reports/query tools. Code is written in this legacy language and executed with the legacy runtime executing environment. The kernel of the system (the one part we cannot change ourselves) transforms the legacy language/forms/queries/ddl into Oracle SQL (or Microsoft or DB2 depending on kernel version - we use the kernel version that runs Oracle.)
So we actually write our inserts in the legacy language (or records are inserted in a legacy form.) The runtime kernel translates it into an Oracle INSERT statement.
When the runtime kernel needs to INSERT a record (in any table), it calls SELECT XAL_SEQ.NEXTVAL FROM DUAL getting the next sequence value. Then it performs the INSERT using the retrieved value. BUT... the system has defined the sequence as INCREMENT BY 10. The runtime kernel now can perform the next 9 INSERT statements (in whatever tables that may be) without calling NEXTVAL.
(This runtime kernel was originally written early 90's using their own legacy database. Then a version of the kernel was written that could execute the same code, but transformed by the kernel into Oracle SQL for replacing the legacy database with SQL. This was done for an Oracle version 7 and hasn't been changed since. The developers probably have thought to improve performance by eliminating the SELECT XAL_SEQ.NEXTVAL FROM DUAL for 9 out of 10 INSERTs - I assume they either did not know of RETURNING clause or the internal architecture of the runtime kernel required them to have the sequence value prior to INSERT and not after. I do not know for sure, it just is, and it is internally in the kernel where I cannot touch it :-(
So I could maybe define a package named the same as the sequence and with a function named NEXTVAL.That ought to work as far as fooling the runtime kernel. But the IOT list of available key values could only contain values with the first of 10 consecutive available values. That would be something I would need to research to see if sufficient "series of 10"s exists to make it a viable solution or if my available keys are mostly scattered in tiny chunks.
But an idea to look into, definitely ;-)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha_at_gmail.com<mailto:kibeha_at_gmail.com>
_at_kibeha
On Thu, Jan 16, 2014 at 7:21 PM, Powell, Mark <mark.powell2_at_hp.com<mailto:mark.powell2_at_hp.com>> wrote: Instead of taking the probably load costly and maybe potentially data corrupting action of revaluing all the existing PK values while maintaining the FK relationships what about the following:
If non-RAC
Gather all available key values into an IOT.
Write a function that executes as an autonomous transaction that selects for update and deletes the next available (min) key value row passing back the value.
Substitute this function call in all code using the current sequence generator
We used this method once when we had a problem with an external system could not handle the sequence generated value being over 5 or 6 digits and we got to that point. This method never caused us any problems but we were not using it to populate all sequence generated key values in our application either.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 16 2014 - 21:46:08 CET