RE: help with fragmentation scheme
I would imagine that you have an index on the column in question, which would obviate the need for fragment elimination. cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of david@smooth1.co.uk Sent: Tuesday, October 27, 2009 3:03 PM To: informix-list@iiug.org Subject: Re: help with fragmentation scheme On 27 Oct, 02:36, Art Kagel <art.ka...@gmail.com> wrote: > Yes, you can fragment on such a complex formula. Just be careful that you > cover all of the possible permutations or have a REMAINDER fragment. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (a...@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. 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 Mon, Oct 26, 2009 at 10:12 AM, Floyd Wellershaus <fl...@fwellers.com>wrote: > > > We have to fragment a table now because of nearing the page limit size. Yes > > we could just change the page size but think at 240million rows, it's > > probably a good thing to fragment the table anyway. > > > Most queries join that table to other tables based on the token and joined > > to another field(stprofil_token). > > The other field has values that are spread througout the token range. So if > > we fragmented on the token field, it would be good to eliminate the proper > > fragment, but that would have to happen many times until it found the right > > record that contains the token/stprofil_token value. > > > So would the best thing be to try and find a good split of data between > > those 2 fields ? > > Like see if I can fragment with an expression like: > > token <=x and token >y and stprofil_token <=a and stprofil_token > b ?? > > > Thanks. > > floyd > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list On the Informix course i did the advice was to ALWAYS have a remainder expression, even if the values in the column are known now they can change in the future. You need to deceide if you want queries to most commonly access a). one fragment (complete fragment elimination), b) some fragments (partial fragment elimination) with or without parallelism c) all fragments with parallelism. This really depends upon what types of queries that will be done and how many cpus/ how much parallel io you can use per query. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
Floyd, I see nothing wrong with your fragmentation scheme, except, perhaps, the way you worded it. For clarity, I would have expressed it as: token >x and token <=y and stprofil_token > a and stprofil_token <=b for token between (x and y) and stprofil between (a and b). In any case, trying to translate into an actual distribution, this declares - 3 ranges for token (token <=y, y < token <=x, token >x) - 3 ranges for stprofil (stprofil <= a, a < stprofil <= b, stprofil > b) To cover all possibilities, you need 9 partitions, plus (as some have pointed out) a remainder partition whether or nor you believe you'll need it. I believe you can set different extent parameters for each partition in release 11+; surely Art will supply the exact version. ;-) Thus, you can set a small extent size for the remainder partition. I would be dubious of Jack's suggestion, however. While it is not essential that an index have a similar fragmentation scheme as its table, the size that Floyd describes tells me that some fragment elimination at the index level might be prudent. For example, the index might be fragmented only on token (assuming the index is a composite of <token, stprofil>) - 3 index partitions + remainder. And while the latest releases of IDS allow multiple fragments of a table to reside in the same DBspace, that is a convenience you should not necessarily indulge if you can afford the additional spindles. You don't want the parallel threads fighting each other for that blessed disk head. As an aside, as long as we are discussing useful new features of IDS, I recall requesting the ability to set extent sizes for an index. Did that ever see the light of day? -- Jacob Jack Parker wrote: > I would imagine that you have an index on the column in question, which > would obviate the need for fragment elimination. > > cheers > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]On Behalf Of david@smooth1.co.uk > Sent: Tuesday, October 27, 2009 3:03 PM > To: informix-list@iiug.org > Subject: Re: help with fragmentation scheme > > > On 27 Oct, 02:36, Art Kagel <art.ka...@gmail.com> wrote: >> Yes, you can fragment on such a complex formula. Just be careful that you >> cover all of the possible permutations or have a REMAINDER fragment. >> >> Art >> >> Art S. Kagel >> Oninit (www.oninit.com) >> IIUG Board of Directors (a...@iiug.org) >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions > and >> do not reflect on my employer, Oninit, the IIUG, nor any other > organization >> with which I am associated either explicitly or implicitly. 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 Mon, Oct 26, 2009 at 10:12 AM, Floyd Wellershaus > <fl...@fwellers.com>wrote: >>> We have to fragment a table now because of nearing the page limit size. > Yes >>> we could just change the page size but think at 240million rows, it's >>> probably a good thing to fragment the table anyway. >>> Most queries join that table to other tables based on the token and > joined >>> to another field(stprofil_token). >>> The other field has values that are spread througout the token range. So > if >>> we fragmented on the token field, it would be good to eliminate the > proper >>> fragment, but that would have to happen many times until it found the > right >>> record that contains the token/stprofil_token value. >>> So would the best thing be to try and find a good split of data between >>> those 2 fields ? >>> Like see if I can fragment with an expression like: >>> token <=x and token >y and stprofil_token <=a and stprofil_token > b ?? >>> Thanks. >>> floyd >>> _______________________________________________ >>> Informix-list mailing list >>> Informix-l...@iiug.org >>> http://www.iiug.org/mailman/listinfo/informix-list > > On the Informix course i did the advice was to ALWAYS have a remainder > expression, even if the values in the column are known now > they can change in the future. > > You need to deceide if you want queries to most commonly access > > a). one fragment (complete fragment elimination), > b) some fragments (partial fragment elimination) with or without > parallelism > c) all fragments with parallelism. > > This really depends upon what types of queries that will be done and > how many cpus/ how much parallel io you can use per query. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >