refragging a table
We have a table that is fragmented by the token, which is a serial datatype. The last fragment has grown too big so we want to make more fragments. Can I just get the max(token) and add a fragment with the expression: (( token > maxtoken ) AND (token <= some other number) ) or do I have to totally refrag the table ? Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/
Is there a better field to fragment on? Something that is evenly distributed among all of your records now and in the future, It being used in queries a lot is also helpful. Floyd Wellershaus wrote: > We have a table that is fragmented by the token, which is a serial datatype. > The last fragment has grown too big so we want to make more fragments. > > Can I just get the max(token) and add a fragment with the expression: > (( token > maxtoken ) AND (token <= some other number) ) > or do I have to totally refrag the table ? > > Thanks, > floyd > > > > > > > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > > > email: fwellers@yahoo.com > > > Home: 703-430-0805 > > > Cell: 703-477-6045 > ======================== > > > http://www.one.org/ > --0-1694373946-1153217090=:82272 > Content-Type: text/html > X-Google-AttachSize: 1328 > > <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV> > <DIV>We have a table that is fragmented by the token, which is a serial datatype.</DIV> > <DIV>The last fragment has grown too big so we want to make more fragments.</DIV> > <DIV> </DIV> > <DIV>Can I just get the max(token) and add a fragment with the expression:</DIV> > <DIV>(( token > maxtoken ) AND (token <= some other number) ) </DIV> > <DIV>or do I have to totally refrag the table ?</DIV> > <DIV> </DIV> > <DIV>Thanks,</DIV> > <DIV>floyd</DIV> > <DIV> </DIV> > <DIV><BR> > <DIV><BR> > <DIV><BR> > <DIV><BR> > <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR> > <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR> > <DIV>Home: 703-430-0805</DIV><BR> > <DIV>Cell: 703-477-6045<BR>========================</DIV><BR> > <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>; > <DIV></DIV></div></body></html> > --0-1694373946-1153217090=:82272--
Apparently that table is joined on a lot by that field. That's why we did it.
I called Informix, and although I don't have an explanation yet, they said I would need to totally refragment the table in order to fix this.
Believe it or not, we are still considering refragging on the token field.
But, instead of making the last clause simply a token > xxxx, we can make the last clause just like the other clauses. This way, all we should have to do is add another fragment to put the latest tokens in. We'd have to keep an eye on the tokens though.
A thought is, can you create a table with enough fragments to last 10 years, but don't actually create the dbspaces until we need them or do all the dbspaces need to be there at the start ?
Also, I notice that my calculations when figuring size didn't pan out. The row size is 122. So doing the math, figuring on a 4 byte slot entry per page, and a usable page size of 4028, I figured that we can fit 31 rows on a page. In actuallity we can fit much more than that.
I assume the difference is due to the varchar fields in the table. The oncheck -pt must report max page size to include the max if all varchars are maxed out, but in reality that rarely happens. I guess we need to plan for the worst though, but it's a waste of space, when planning out fragments.
Thanks for any advice.
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: bozon <curtis@crowson1.com>
To: informix-list@iiug.org
Sent: Tuesday, July 18, 2006 10:11:57 AM
Subject: Re: refragging a table
Is there a better field to fragment on? Something that is evenly
distributed among all of your records now and in the future, It being
used in queries a lot is also helpful.
Floyd Wellershaus wrote:
> We have a table that is fragmented by the token, which is a serial datatype.
> The last fragment has grown too big so we want to make more fragments.
>
> Can I just get the max(token) and add a fragment with the expression:
> (( token > maxtoken ) AND (token <= some other number) )
> or do I have to totally refrag the table ?
>
> Thanks,
> floyd
>
>
>
>
>
>
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
>
> email: fwellers@yahoo.com
>
>
> Home: 703-430-0805
>
>
> Cell: 703-477-6045
> ========================
>
>
> http://www.one.org/
> --0-1694373946-1153217090=:82272
> Content-Type: text/html
> X-Google-AttachSize: 1328
>
> <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> <DIV>We have a table that is fragmented by the token, which is a serial datatype.</DIV>
> <DIV>The last fragment has grown too big so we want to make more fragments.</DIV>
> <DIV> </DIV>
> <DIV>Can I just get the max(token) and add a fragment with the expression:</DIV>
> <DIV>(( token > maxtoken ) AND (token <= some other number) ) </DIV>
> <DIV>or do I have to totally refrag the table ?</DIV>
> <DIV> </DIV>
> <DIV>Thanks,</DIV>
> <DIV>floyd</DIV>
> <DIV> </DIV>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> <DIV>Home: 703-430-0805</DIV><BR>
> <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>;
> <DIV></DIV></div></body></html>
> --0-1694373946-1153217090=:82272--
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
I believe the dbspaces need to be there. If you are running on 10 you
can put all of the fragments in the same dbspace. If you aren't maybe
this would be a good enough reason to upgrade. ;-)
Floyd Wellershaus wrote:
> Apparently that table is joined on a lot by that field. That's why we did it.
> I called Informix, and although I don't have an explanation yet, they said I would need to totally refragment the table in order to fix this.
>
> Believe it or not, we are still considering refragging on the token field.
> But, instead of making the last clause simply a token > xxxx, we can make the last clause just like the other clauses. This way, all we should have to do is add another fragment to put the latest tokens in. We'd have to keep an eye on the tokens though.
>
> A thought is, can you create a table with enough fragments to last 10 years, but don't actually create the dbspaces until we need them or do all the dbspaces need to be there at the start ?
>
> Also, I notice that my calculations when figuring size didn't pan out. The row size is 122. So doing the math, figuring on a 4 byte slot entry per page, and a usable page size of 4028, I figured that we can fit 31 rows on a page. In actuallity we can fit much more than that.
> I assume the difference is due to the varchar fields in the table. The oncheck -pt must report max page size to include the max if all varchars are maxed out, but in reality that rarely happens. I guess we need to plan for the worst though, but it's a waste of space, when planning out fragments.
>
> Thanks for any advice.
>
>
>
>
>
>
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
>
> email: fwellers@yahoo.com
>
>
> Home: 703-430-0805
>
>
> Cell: 703-477-6045
> ========================
>
>
> http://www.one.org/
>
>
>
> ----- Original Message ----
> From: bozon <curtis@crowson1.com>
> To: informix-list@iiug.org
> Sent: Tuesday, July 18, 2006 10:11:57 AM
> Subject: Re: refragging a table
>
>
> Is there a better field to fragment on? Something that is evenly
> distributed among all of your records now and in the future, It being
> used in queries a lot is also helpful.
>
>
>
> Floyd Wellershaus wrote:
> > We have a table that is fragmented by the token, which is a serial datatype.
> > The last fragment has grown too big so we want to make more fragments.
> >
> > Can I just get the max(token) and add a fragment with the expression:
> > (( token > maxtoken ) AND (token <= some other number) )
> > or do I have to totally refrag the table ?
> >
> > Thanks,
> > floyd
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ========================
> > -<<Floyd Wellershaus>>-
> > Database Administrator
> > Unix Administrator
> >
> >
> >
> > email: fwellers@yahoo.com
> >
> >
> > Home: 703-430-0805
> >
> >
> > Cell: 703-477-6045
> > ========================
> >
> >
> > http://www.one.org/
> > --0-1694373946-1153217090=:82272
> > Content-Type: text/html
> > X-Google-AttachSize: 1328
> >
> > <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> > <DIV>We have a table that is fragmented by the token, which is a serial datatype.</DIV>
> > <DIV>The last fragment has grown too big so we want to make more fragments.</DIV>
> > <DIV> </DIV>
> > <DIV>Can I just get the max(token) and add a fragment with the expression:</DIV>
> > <DIV>(( token > maxtoken ) AND (token <= some other number) ) </DIV>
> > <DIV>or do I have to totally refrag the table ?</DIV>
> > <DIV> </DIV>
> > <DIV>Thanks,</DIV>
> > <DIV>floyd</DIV>
> > <DIV> </DIV>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> > <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> > <DIV>Home: 703-430-0805</DIV><BR>
> > <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> > <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>;
> > <DIV></DIV></div></body></html>
> > --0-1694373946-1153217090=:82272--
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> --0-1107657720-1153235869=:91156
> Content-Type: text/html
> X-Google-AttachSize: 6677
>
> <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> <DIV>Apparently that table is joined on a lot by that field. That's why we did it.</DIV>
> <DIV>I called Informix, and although I don't have an explanation yet, they said I would need to totally refragment the table in order to fix this.</DIV>
> <DIV> </DIV>
> <DIV>Believe it or not, we are still considering refragging on the token field.</DIV>
> <DIV>But, instead of making the last clause simply a token > xxxx, we can make the last clause just like the other clauses. This way, all we should have to do is add another fragment to put the latest tokens in. We'd have to keep an eye on the tokens though.</DIV>
> <DIV> </DIV>
> <DIV>A thought is, can you create a table with enough fragments to last 10 years, but don't actually create the dbspaces until we need them or do all the dbspaces need to be there at the start ?</DIV>
> <DIV> </DIV>
> <DIV>Also, I notice that my calculations when figuring size didn't pan out. The row size is 122. So doing the math, figuring on a 4 byte slot entry per page, and a usable page size of 4028, I figured that we can fit 31 rows on a page. In actuallity we can fit much more than that. </DIV>
> <DIV>I assume the difference is due to the varchar fields in the table. The oncheck -pt must report max page size to include the max if all varchars are maxed out, but in reality that rarely happens. I guess we need to plan for the worst though, but it's a waste of space, when planning out fragments.</DIV>
> <DIV> </DIV>
> <DIV>Thanks for any advice.<BR> </DIV>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> <DIV>Home: 703-430-0805</DIV><BR>
> <DIV>Cell: 703-477-6045<BR>========================<
Umm, why would I want to put all the fragments in the same dbspace ? Doesn't that defeat the main purpose of fragmenting ?
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: bozon <curtis@crowson1.com>
To: informix-list@iiug.org
Sent: Tuesday, July 18, 2006 2:44:25 PM
Subject: Re: refragging a table
I believe the dbspaces need to be there. If you are running on 10 you
can put all of the fragments in the same dbspace. If you aren't maybe
this would be a good enough reason to upgrade. ;-)
Floyd Wellershaus wrote:
> Apparently that table is joined on a lot by that field. That's why we did it.
> I called Informix, and although I don't have an explanation yet, they said I would need to totally refragment the table in order to fix this.
>
> Believe it or not, we are still considering refragging on the token field.
> But, instead of making the last clause simply a token > xxxx, we can make the last clause just like the other clauses. This way, all we should have to do is add another fragment to put the latest tokens in. We'd have to keep an eye on the tokens though.
>
> A thought is, can you create a table with enough fragments to last 10 years, but don't actually create the dbspaces until we need them or do all the dbspaces need to be there at the start ?
>
> Also, I notice that my calculations when figuring size didn't pan out. The row size is 122. So doing the math, figuring on a 4 byte slot entry per page, and a usable page size of 4028, I figured that we can fit 31 rows on a page. In actuallity we can fit much more than that.
> I assume the difference is due to the varchar fields in the table. The oncheck -pt must report max page size to include the max if all varchars are maxed out, but in reality that rarely happens. I guess we need to plan for the worst though, but it's a waste of space, when planning out fragments.
>
> Thanks for any advice.
>
>
>
>
>
>
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
>
> email: fwellers@yahoo.com
>
>
> Home: 703-430-0805
>
>
> Cell: 703-477-6045
> ========================
>
>
> http://www.one.org/
>
>
>
> ----- Original Message ----
> From: bozon <curtis@crowson1.com>
> To: informix-list@iiug.org
> Sent: Tuesday, July 18, 2006 10:11:57 AM
> Subject: Re: refragging a table
>
>
> Is there a better field to fragment on? Something that is evenly
> distributed among all of your records now and in the future, It being
> used in queries a lot is also helpful.
>
>
>
> Floyd Wellershaus wrote:
> > We have a table that is fragmented by the token, which is a serial datatype.
> > The last fragment has grown too big so we want to make more fragments.
> >
> > Can I just get the max(token) and add a fragment with the expression:
> > (( token > maxtoken ) AND (token <= some other number) )
> > or do I have to totally refrag the table ?
> >
> > Thanks,
> > floyd
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ========================
> > -<<Floyd Wellershaus>>-
> > Database Administrator
> > Unix Administrator
> >
> >
> >
> > email: fwellers@yahoo.com
> >
> >
> > Home: 703-430-0805
> >
> >
> > Cell: 703-477-6045
> > ========================
> >
> >
> > http://www.one.org/
> > --0-1694373946-1153217090=:82272
> > Content-Type: text/html
> > X-Google-AttachSize: 1328
> >
> > <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> > <DIV>We have a table that is fragmented by the token, which is a serial datatype.</DIV>
> > <DIV>The last fragment has grown too big so we want to make more fragments.</DIV>
> > <DIV> </DIV>
> > <DIV>Can I just get the max(token) and add a fragment with the expression:</DIV>
> > <DIV>(( token > maxtoken ) AND (token <= some other number) ) </DIV>
> > <DIV>or do I have to totally refrag the table ?</DIV>
> > <DIV> </DIV>
> > <DIV>Thanks,</DIV>
> > <DIV>floyd</DIV>
> > <DIV> </DIV>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> > <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> > <DIV>Home: 703-430-0805</DIV><BR>
> > <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> > <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>;
> > <DIV></DIV></div></body></html>
> > --0-1694373946-1153217090=:82272--
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> --0-1107657720-1153235869=:91156
> Content-Type: text/html
> X-Google-AttachSize: 6677
>
> <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> <DIV>Apparently that table is joined on a lot by that field. That's why we did it.</DIV>
> <DIV>I called Informix, and although I don't have an explanation yet, they said I would need to totally refragment the table in order to fix this.</DIV>
> <DIV> </DIV>
> <DIV>Believe it or not, we are still considering refragging on the token field.</DIV>
> <DIV>But, instead of making the last clause simply a token > xxxx, we can make the last clause just like the other clauses. This way, all we should have to do is add another fragment to put the latest tokens in. We'd have to keep an eye on the tokens though.</DIV>
> <DIV> </DIV>
> <DIV>A thought is, can you create a table with enough fragments to last 10 years, but don't actually create the dbspaces until we need them or do all the dbspaces need to be there at the start ?</DIV>
> <DIV> </DIV>
> <DIV>Also, I notice that my calculations when figuring size didn't pan out. The row size is 122. So doing the math, figuring on a 4 byte slot entry per page, and a usable page size of 4028, I figured that we can fit 31 rows on a page. In actuallity we can fit much more than that. </DIV>
> <DIV>I assume the difference is due to the varchar fields in the table. The oncheck -pt must report max page size to include the max if all varchars are maxed out, but in reality that rarely happens. I guess we need to plan for the worst though, but it's a waste of space, when planning out fragments.</DIV>
> <DIV> </DIV>
> <DIV>Th