Re: Keys larger than 2 Billion
At 12:07 AM 9/14/96 -0400, you wrote:
>We have an application where we want to plan for some of our
>tables to have more than 2 billion rows. Currently we have a
>primary key that is a SERIAL type column, but this will only
>handle unique IDs up to 2 billion rows. We have thought about
>having a composite primary key with the first value being a
>julian day or some kind of month and a second column that is
>serial. Another idea is to handle incrementing ourselves by
>storing a current value in some control table, but this will add
>more overhead to an insert.
>
>Does anyone have any input on this? Has anyone seen anything
>like this done? I am sure that there are probably a hundred
>ways to do this, I am looking for a good discussion of the pros
>and cons of different approaches.
>=====
>
>
table main(
key1 date,
key2 integer,
primary key(key1,key2)
);
table GenKeySerial(
NextValue serial(1)
);
have a central function to handle the assingments for key values, remember
that you can have this function to RE-CREATE the GenKeySerial table every
month, year or whenever depending on your data growing.
Informi-4gl
-- sintax Let Record.SerialField = CentralAssignments()
function CentralAssignments()
define
NextVal integer
if (the conditions changed, example change of month, change of year or
whatever you want) then
-- Reset it
drop table GenKeySerial
create table GenKeySerial(
NextValue serial(1)
)
end if
insert into GenKeySerial values(0) --
let NextVal = sqlca.sqlerrd[2] -- returng the serial number inserted
return NextVal
end function
you can add a cron sqlcript to delete the values inserted in the
GenKeySerial table, it is just a sample of what you can handle, but I know
yours will be a more complex function.
I hope this help,
Regards,
--------------------------------------------
Mario Estrada Rosa | Company (LESCO,S.A.)
Systems Engineer |
Phone (502) 3318116 | Fax (502) 3348447
(502) 3319268 |
(502) 3348446 | Email lesco@guate.net
Central Time Applies|
--------------------------------------------