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 18:21:54 +0000
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.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kim Berg Hansen
Sent: Thursday, January 16, 2014 7:28 AM
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Question on IOT with ascending primary key with a twist
Makes sense, thanks.
The reason I am considering IOT is to make selects on the view joining the
mview and IOT as "good" as possible - one less buffer visit I think is good
since there will be selects (pk access typically one row) of the view on the
order of tens to hundreds a second... Wasting a bit of space to improve the
selects will probably be worth it to me.
As for what I'll do when our 32-bit signed integer PK reaches zero? Well, that
will happen late 2015/early 2016 (curve extrapolated from data from when the
application rolled out in 1995 and taking increase in business into
consideration.) The sequence is shared for pseudo key for about 1000 tables - a
lot of that is deleted over time (for example order table is inserted, then
deleted upon invoicing when a new record is created in invoice table), which
means that even though the sequence will be exhausted in two years, there is an
awful lot of "now unused numbers". So at present the plan is to "re-number" all
records in all tables (including the "foreign key" references) and then let the
sequence start at max(pk) + 1 after the "re-numbering." Such a plan probably
will buy us some 6-8 years grace before we are forced to consider a new
application (but boss will stick to old application as long as we possibly can
keep it alive ;-)
But that is something I still need to research a good deal. My plan is actually
to ask the L-list for good ideas after I have done some homework first ;-)
Thanks for the IOT advice.
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha
On Thu, Jan 16, 2014 at 12:38 PM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
Other related posts:
