Re: help with fragmentation scheme
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 (art@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 <floyd@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-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >
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.