Serial to Serial8
User asked about converting a SERIAL column to SERIAL8 for a 2-billion-row table, concerned the operation would take forever. Experts recommended upgrading to IDS 11.70.xC7W1 or higher where the conversion is an in-place online alter table operation. They also suggested using BIGSERIAL instead of SERIAL8 as it's more efficient and maps directly to 64-bit integers in host languages. User decided to upgrade to IDS 11.70+.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks,
A table is approaching to 2 billions of rows ... to blow up a
SERIAL column c1.
But ,
alter table mytable modify c1 SERIAL8,
would take forever ?
Any other ideas there?
Thanks,
Frank
--089e0149ccdefbbdbe04dd510aa9
On 22/05/13 17:27, FRANK wrote:
> Folks,
>
> A table is approaching to 2 billions of rows ... to blow up a
> SERIAL column c1.
>
> But ,
>
> alter table mytable modify c1 SERIAL8,>
> would take forever ?
>
> Any other ideas there?
>
> Thanks,
> Frank
>
> --089e0149ccdefbbdbe04dd510aa9
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
In 11.70.xC7W1 it is done as an in place alter - Any indexes on the column
would be silently rebuilt though.
Also - best to use a bigserial rather than a serial8.
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Rename the table to mytable_old, create a new table mytable_new with the
SERIAL8 field with a starting value of 2^32, and replace mytable with a
UNION view of the two tables mapping the SERIAL in mytable_old to SERIAL8.
Over time you can move batches of records from mytable_old to mytable_new
retaining their serial column value and deleting the original rows from
mytable_old. Once everything has been moved over you can drop mytable_old
and the mytable view and rename mytable_new.
If there are dependent tables, it gets more complex, but you can do the
same old_new shuffle for those tables as well with the _new tables
dependent on mytable_new and the _old tables dependent on mytable_old, etc.
and move and delete related sets of rows all together in transactions.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, May 22, 2013 at 12:27 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Folks,
>
> A table is approaching to 2 billions of rows ... to blow up a
> SERIAL column c1.
>
> But ,
>
> alter table mytable modify c1 SERIAL8,>
> would take forever ?
>
> Any other ideas there?
>
> Thanks,
> Frank
>
> --089e0149ccdefbbdbe04dd510aa9
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0160a3b86fe1b204dd517b59
Frank:
I have two suggestions for you.
1 I would look at using bigserials as these only consume 8 bytes per value
while serial8 consume 10 bytes of space per value.
2 I would suggest upgrading to 11.70.xC7W1 or higher. In these versions
the alter for a serial type to a bigserial type is an online alter table
and
does not re-write the entire table. Currently many types are online
operations
but the serial to bigserial was missed, and now it is included in the
family of
online alter tables (along with int to bigint).
John F. Miller III
ids-bounces@iiug.org wrote on 05/22/2013 09:27:44 AM:
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org,
> Date: 05/22/2013 09:30 AM
> Subject: Serial to Serial8 [30318]
> Sent by: ids-bounces@iiug.org
>
> Folks,
>
> A table is approaching to 2 billions of rows ... to blow up a
> SERIAL column c1.
>
> But ,
>
> alter table mytable modify c1 SERIAL8,>
> would take forever ?
>
> Any other ideas there?
>
> Thanks,
> Frank
>
> --089e0149ccdefbbdbe04dd510aa9
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I agree with Marco that BIGSERIAL is better than SERIAL8 simply because it
maps directly to a 64bit integer in host languages (long long in 32bit C,
long in 64bit C) whereas SERIAL8 needs to be treated specially and accessed
only through library functions.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, May 22, 2013 at 12:41 PM, Marco Greco <marco@4glworks.com> wrote:
> On 22/05/13 17:27, FRANK wrote:
> > Folks,
> >
> > A table is approaching to 2 billions of rows ... to blow up a
> > SERIAL column c1.
> >
> > But ,
> >
> > alter table mytable modify c1 SERIAL8,> >
> > would take forever ?
> >
> > Any other ideas there?
> >
> > Thanks,
> > Frank
> >
> > --089e0149ccdefbbdbe04dd510aa9
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> In 11.70.xC7W1 it is done as an in place alter - Any indexes on the column
> would be silently rebuilt though.
> Also - best to use a bigserial rather than a serial8.
> --
> Ciao,
> Marco
>
>
______________________________________________________________________________
> Marco Greco /UK /IBM Standard disclaimers apply!
>
> Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
> 4glworks http://www.4glworks.com
> Informix on Linux http://www.4glworks.com/ifmxlinux.htm
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0158ad54b8907a04dd5187be
Big Thanks to Art, John and Marco!
We will upgrade to IDS11.70 + first ( as John suggested) :-)
Frank
On Wed, May 22, 2013 at 1:02 PM, Art Kagel <art.kagel@gmail.com> wrote:
> I agree with Marco that BIGSERIAL is better than SERIAL8 simply because it
> maps directly to a 64bit integer in host languages (long long in 32bit C,
> long in 64bit C) whereas SERIAL8 needs to be treated specially and accessed
> only through library functions.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Wed, May 22, 2013 at 12:41 PM, Marco Greco <marco@4glworks.com> wrote:
>
> > On 22/05/13 17:27, FRANK wrote:
> > > Folks,
> > >
> > > A table is approaching to 2 billions of rows ... to blow up a
> > > SERIAL column c1.
> > >
> > > But ,
> > >
> > > alter table mytable modify c1 SERIAL8,> > >
> > > would take forever ?
> > >
> > > Any other ideas there?
> > >
> > > Thanks,
> > > Frank
> > >
> > > --089e0149ccdefbbdbe04dd510aa9
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > In 11.70.xC7W1 it is done as an in place alter - Any indexes on the
> column
> > would be silently rebuilt though.
> > Also - best to use a bigserial rather than a serial8.
> > --
> > Ciao,
> > Marco
> >
> >
>
>
______________________________________________________________________________
> > Marco Greco /UK /IBM Standard disclaimers apply!
> >
> > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
> > 4glworks http://www.4glworks.com
> > Informix on Linux http://www.4glworks.com/ifmxlinux.htm
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e0158ad54b8907a04dd5187be
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf30684a715d134504dd51cbc1