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

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_at_gmail.com
_at_kibeha

On Thu, Jan 16, 2014 at 12:38 PM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
>
>

--
http://www.freelists.org/webpage/oracle-l