RE: Question on IOT with ascending primary key with a twist
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Jan 2014 20:46:08 +0000
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@xxxxxxxxx]
Sent: Thursday, January 16, 2014 3:04 PM
To: Powell, Mark
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha
On Thu, Jan 16, 2014 at 7:21 PM, Powell, Mark
<mark.powell2@xxxxxx<mailto:mark.powell2@xxxxxx>> 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.
Other related posts:
