Re: Question on IOT with ascending primary key with a twist

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
_at_kibeha

On Thu, Jan 16, 2014 at 7:21 PM, Powell, Mark <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-l