Fragmenting a large nearly full framented table
A new Informix DBA asked how to expand/fragment a single unfragmented, indexed table of ~260 million rows that is nearly full, without a long table lock or outage. Suggestions were: move data in small batches using WHERE clauses on an index (locking only selected rows, watching transaction/log size and LOCKS), or use a hold cursor with frequent commits (ESQL/C, Perl, 4GL, SPL); also detach indexes (checking used pages vs data pages via oncheck -pt) as a stopgap before going round-robin fragmented, noting index drop/recreate also locks the table. The poster found used and data pages nearly equal, so detaching indexes wouldn't help, and the reorg was estimated to take days rather than the acceptable 8 hours. Another reply questioned that estimate and asked about table volume and CPU count; no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, looking for a bit of advice here as a new Informix user. One of our Clients has a very large unfragmented indexed table of roughly 260 million rows. It is approaching full capacity and requires expanding. Any one got any ideas? The transfering of some of the data from one table to another before creating a fragmented table from the two tables might be an option, but can the transfer of 100 million or so rows be done without locking the table. Thanks in advance. John
Hi,
if you have an appropriate index on the table you can move small
portions of the table at a time with standard tools like dbaccess
and appropriate WHERE clauses - only the selected rows get locked.
Take care of maximum transaction sizes (logfiles) and locking
restrictions (LOCKS parameter).
Another solution is to work with a Hold Cursor and doing frequent
commits. That requires some programming (ESQL/C, Perl, 4GL, SPL).
Regards,
Andreas
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Urspr|ngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von JOHN KENNEDY
> Gesendet: Freitag, 14. Oktober 2005 17:05
> An: ids@iiug.org
> Betreff: Fragmenting a large nearly full framented table [5834]
>
>
> Hello,
>
> looking for a bit of advice here as a new Informix user. One
> of our Clients has a very large unfragmented indexed table of
> roughly 260 million rows. It is approaching full capacity and
> requires expanding.
>
> Any one got any ideas? The transfering of some of the data
> from one table to another before creating a fragmented table
> from the two tables might be an option, but can the transfer
> of 100 million or so rows be done without locking the table.
>
> Thanks in advance.
>
> John
Detaching
the indexes will help you for a while, after that you will
need to fragm the table,
Round robin....
Diane
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Andreas.KUT....
Sent: Monday, October 17, 2005 6:23 AM
To: ids@iiug.org
Subject: AW: Fragmenting a large nearly full framented table [5839]
Hi,
if you have an appropriate index on the table you can move small
portions of the table at a time with standard tools like dbaccess and
appropriate WHERE clauses - only the selected rows get locked.
Take care of maximum transaction sizes (logfiles) and locking
restrictions (LOCKS parameter).
Another solution is to work with a Hold Cursor and doing frequent
commits. That requires some programming (ESQL/C, Perl, 4GL, SPL).
Regards,
Andreas
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Urspr|ngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von JOHN KENNEDY
> Gesendet: Freitag, 14. Oktober 2005 17:05
> An: ids@iiug.org
> Betreff: Fragmenting a large nearly full framented table [5834]
>
>
> Hello,
>
> looking for a bit of advice here as a new Informix user. One of our
> Clients has a very large unfragmented indexed table of roughly 260
> million rows. It is approaching full capacity and requires expanding.
>
> Any one got any ideas? The transfering of some of the data from one
> table to another before creating a fragmented table from the two
> tables might be an option, but can the transfer of 100 million or so
> rows be done without locking the table.
>
> Thanks in advance.
>
> John
Hi,
having a look at the (attached) indices could be a very good idea. Run
oncheck -pt <database>:<table> | moreand check the used pages against data pages (only works
like intended if you don't have binary/text/byte columns) .
If 'used pages' is much higher than 'data pages' detaching
the indices might be quite helpful. But be aware that dropping
and recreating the indices will lock the whole table, too! And
dropping a not detached index can take some while!
Regards,
Andreas Kutsche
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Urspr|ngliche Nachricht-----
> Von: Lavoie, Diane [mailto:diane.lavoie@domtar.com]
> Gesendet: Montag, 17. Oktober 2005 14:10
> An: KUTSCHE Andreas (HZ422); ids@iiug.org
> Betreff: RE: Fragmenting a large nearly full framented table [5839]
>
>
> Detaching the indexes will help you for a while, after that you will
> need to fragm the table,
> Round robin....
>
> Diane
>
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
> Behalf Of Andreas.KUT....
> Sent: Monday, October 17, 2005 6:23 AM
> To: ids@iiug.org
> Subject: AW: Fragmenting a large nearly full framented table [5839]
>
> Hi,
>
> if you have an appropriate index on the table you can move small
> portions of the table at a time with standard tools like dbaccess and
> appropriate WHERE clauses - only the selected rows get locked.
> Take care of maximum transaction sizes (logfiles) and locking
> restrictions (LOCKS parameter).
>
> Another solution is to work with a Hold Cursor and doing frequent
> commits. That requires some programming (ESQL/C, Perl, 4GL, SPL).
>
> Regards,
> Andreas
>
> >
> -------------------------------------------
> SPAR Oesterreichische Warenhandels-AG
> Hauptzentrale
> Europastrasse 3
> A - 5015 Salzburg
>
> Tel: +43 662 4470 24223
> Mobile: +43 664 6259575
> E-Mail: Andreas.KUTSCHE@spar.at
> Internet: http://www.spar.at
> -------------------------------------------
> -----Urspr|ngliche Nachricht-----
>
> > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> > Auftrag von JOHN KENNEDY
> > Gesendet: Freitag, 14. Oktober 2005 17:05
> > An: ids@iiug.org
> > Betreff: Fragmenting a large nearly full framented table [5834]
> >
> >
> > Hello,
> >
> > looking for a bit of advice here as a new Informix user. One of our
> > Clients has a very large unfragmented indexed table of roughly 260
> > million rows. It is approaching full capacity and requires
> expanding.
> >
> > Any one got any ideas? The transfering of some of the data from one
> > table to another before creating a fragmented table from the two
> > tables might be an option, but can the transfer of 100
> million or so
> > rows be done without locking the table.
> >
> > Thanks in advance.
> >
> > John
Thanks for that,
Sadly the used pages and data pages are very nearly the same, so I am
not sure that removing the indexes will prove amazingly useful.
I guess the ideal solution would be to attach an additional table to
this one. And then try and spread the data evenly across the two and
instigate a round robin filling of the table.
However the main stumbling block is still the transfer/deletion of a
large number of rows that will cause an outage or locking of the table.
Up to 8 hours or so would not be a problem, and even multiple 8 hour
outages spread over a week or so would not be too bad, however we have
worked out that it would take a number of days outage, which is not
desirable.
Cheers
John
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Andreas.KUT....
Sent: 17 October 2005 13:28
To: ids@iiug.org
Subject: AW: Fragmenting a large nearly full framented table [5842]
Hi,
having a look at the (attached) indices could be a very good idea. Run
oncheck -pt <database>:<table> | moreand check the used pages against data pages (only works
like intended if you don't have binary/text/byte columns) .
If 'used pages' is much higher than 'data pages' detaching the indices
might be quite helpful. But be aware that dropping and recreating the
indices will lock the whole table, too! And dropping a not detached
index can take some while!
Regards,
Andreas Kutsche
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Urspr|ngliche Nachricht-----
> Von: Lavoie, Diane [mailto:diane.lavoie@domtar.com]
> Gesendet: Montag, 17. Oktober 2005 14:10
> An: KUTSCHE Andreas (HZ422); ids@iiug.org
> Betreff: RE: Fragmenting a large nearly full framented table [5839]
>
>
> Detaching the indexes will help you for a while, after that you will
> need to fragm the table, Round robin....
>
> Diane
>
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
> Behalf Of Andreas.KUT....
> Sent: Monday, October 17, 2005 6:23 AM
> To: ids@iiug.org
> Subject: AW: Fragmenting a large nearly full framented table [5839]
>
> Hi,
>
> if you have an appropriate index on the table you can move small
> portions of the table at a time with standard tools like dbaccess and
> appropriate WHERE clauses - only the selected rows get locked.
> Take care of maximum transaction sizes (logfiles) and locking
> restrictions (LOCKS parameter).
>
> Another solution is to work with a Hold Cursor and doing frequent
> commits. That requires some programming (ESQL/C, Perl, 4GL, SPL).
>
> Regards,
> Andreas
>
> >
> -------------------------------------------
> SPAR Oesterreichische Warenhandels-AG
> Hauptzentrale
> Europastrasse 3
> A - 5015 Salzburg
>
> Tel: +43 662 4470 24223
> Mobile: +43 664 6259575
> E-Mail: Andreas.KUTSCHE@spar.at
> Internet: http://www.spar.at
> -------------------------------------------
> -----Urspr|ngliche Nachricht-----
>
> > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> > Auftrag von JOHN KENNEDY
> > Gesendet: Freitag, 14. Oktober 2005 17:05
> > An: ids@iiug.org
> > Betreff: Fragmenting a large nearly full framented table [5834]
> >
> >
> > Hello,
> >
> > looking for a bit of advice here as a new Informix user. One of our
> > Clients has a very large unfragmented indexed table of roughly 260
> > million rows. It is approaching full capacity and requires
> expanding.
> >
> > Any one got any ideas? The transfering of some of the data from one
> > table to another before creating a fragmented table from the two
> > tables might be an option, but can the transfer of 100
> million or so
> > rows be done without locking the table.
> >
> > Thanks in advance.
> >
> > John
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an intended
recipient then please promptly delete this e-mail and any attachment and all
copies and inform the sender. Thank you.
How big
(volume wise) is the table? How many CPUs on the machine? I cannot
imagine not being able to reorg 260 million rows in 8 hours, unless the
rowsize were prohibitive.
j.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Kennedy, John
Sent: Monday, October 17, 2005 9:43 AM
To: ids@iiug.org
Subject: RE: Fragmenting a large nearly full framented table [5843]
Thanks for that,
Sadly the used pages and data pages are very nearly the same, so I am
not sure that removing the indexes will prove amazingly useful.
I guess the ideal solution would be to attach an additional table to
this one. And then try and spread the data evenly across the two and
instigate a round robin filling of the table.
However the main stumbling block is still the transfer/deletion of a
large number of rows that will cause an outage or locking of the table.
Up to 8 hours or so would not be a problem, and even multiple 8 hour
outages spread over a week or so would not be too bad, however we have
worked out that it would take a number of days outage, which is not
desirable.
Cheers
John
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Andreas.KUT....
Sent: 17 October 2005 13:28
To: ids@iiug.org
Subject: AW: Fragmenting a large nearly full framented table [5842]
Hi,
having a look at the (attached) indices could be a very good idea. Run
oncheck -pt <database>:<table> | moreand check the used pages against data pages (only works
like intended if you don't have binary/text/byte columns) .
If 'used pages' is much higher than 'data pages' detaching the indices
might be quite helpful. But be aware that dropping and recreating the
indices will lock the whole table, too! And dropping a not detached
index can take some while!
Regards,
Andreas Kutsche
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Urspr|ngliche Nachricht-----
> Von: Lavoie, Diane [mailto:diane.lavoie@domtar.com]
> Gesendet: Montag, 17. Oktober 2005 14:10
> An: KUTSCHE Andreas (HZ422); ids@iiug.org
> Betreff: RE: Fragmenting a large nearly full framented table [5839]
>
>
> Detaching the indexes will help you for a while, after that you will
> need to fragm the table, Round robin....
>
> Diane
>
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
> Behalf Of Andreas.KUT....
> Sent: Monday, October 17, 2005 6:23 AM
> To: ids@iiug.org
> Subject: AW: Fragmenting a large nearly full framented table [5839]
>
> Hi,
>
> if you have an appropriate index on the table you can move small
> portions of the table at a time with standard tools like dbaccess and
> appropriate WHERE clauses - only the selected rows get locked.
> Take care of maximum transaction sizes (logfiles) and locking
> restrictions (LOCKS parameter).
>
> Another solution is to work with a Hold Cursor and doing frequent
> commits. That requires some programming (ESQL/C, Perl, 4GL, SPL).
>
> Regards,
> Andreas
>
> >
> -------------------------------------------
> SPAR Oesterreichische Warenhandels-AG
> Hauptzentrale
> Europastrasse 3
> A - 5015 Salzburg
>
> Tel: +43 662 4470 24223
> Mobile: +43 664 6259575
> E-Mail: Andreas.KUTSCHE@spar.at
> Internet: http://www.spar.at
> -------------------------------------------
> -----Urspr|ngliche Nachricht-----
>
> > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> > Auftrag von JOHN KENNEDY
> > Gesendet: Freitag, 14. Oktober 2005 17:05
> > An: ids@iiug.org
> > Betreff: Fragmenting a large nearly full framented table [5834]
> >
> >
> > Hello,
> >
> > looking for a bit of advice here as a new Informix user. One of our
> > Clients has a very large unfragmented indexed table of roughly 260
> > million rows. It is approaching full capacity and requires
> expanding.
> >
> > Any one got any ideas? The transfering of some of the data from one
> > table to another before creating a fragmented table from the two
> > tables might be an option, but can the transfer of 100
> million or so
> > rows be done without locking the table.
> >
> > Thanks in advance.
> >
> > John
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an
intended recipient then please promptly delete this e-mail and any
attachment and all copies and inform the sender. Thank you.