Speed of "alter fragment"?

I'd like to fragment two large tables in three or four fragments, based on dates -they're 20.000.000 rows * 18bytes and 43.000.000 rows * 54 bytes. (The rest of the tables are too small to bother with) There's two large indexes on both tables and I'm planning on detaching them, either in two large dbspaces (one for each table) or using the same fragmenting technique as for the data. Our queries indicates fragments for today, last 12 months and "the lot" -and I wonder if Informix (7.31UC2) resorts the whole table(s) or just moves the offending records? I also wonder if the're much to win in fragmenting indexes. Thomas

Hi Thomas, two things - make sure you use the DATE function when defining the fragmentation expression, e.g. use date_col >= DATE("01/01/2000") and ... rather than date_col >= "01/01/2000" and ... the other thing - use the same fragmentation scheme for indexes - SAT or 'Same As Table', as this allows for quickly attaching and detaching of fragments. For this to work, you'll need to follow all the steps to minimize data movement as outlined in the IDS Performance Guide, and set the AFA_DSCANCHK environment variable prior to starting your 7.31 server. If you have any further questions, contact me directly. Cheers, Erik Thomas Parsli wrote: > I'd like to fragment two large tables in three or four fragments, > based on dates -they're 20.000.000 rows * 18bytes and 43.000.000 > rows * 54 bytes. > (The rest of the tables are too small to bother with) > > There's two large indexes on both tables and I'm planning on detaching > them, either in two large dbspaces (one for each table) or using > the same fragmenting technique as for the data. > > Our queries indicates fragments for today, last 12 months and "the > lot" -and I wonder if Informix (7.31UC2) resorts the whole table(s) > or just moves the offending records? > > I also wonder if the're much to win in fragmenting indexes. > > Thomas

In article <37CE10E5.D1F8D2FA@informix.com>, Erik van Veen <erikv@informix.com> writes >Hi Thomas, > >minimize data movement as outlined in the IDS Performance Guide, and set >the AFA_DSCANCHK environment variable prior to starting your 7.31 server. ?? What is this environment variable? -- David Williams

AFA_DSCANCHK is a fix in 7.31. Essentially, you apply a check constraint to your non-fragmented table (that you plan to attach to a fragmented one) prior to attaching. The check constraint ensures that all records in said table belong there. If AFA_DSCANCHK has been set when the server instance has been started, the alter fragment attach command will only take sub-seconds to complete - as long as the check constraint is the same as the fragment expression for the new fragment. This is the case because IDS will rely on the fact that the data in the new fragment has already been verified against the new expression. You still pay the price for checking the data in the new candidate fragment, but you can do this as a stand-alone table and thus not disable the fragmented table for a long period of time during the attach. Date is a perfect application of this as date does not index well. Erik was the Informix guy that uncovered this gem for us. Mark David Williams wrote: > > In article <37CE10E5.D1F8D2FA@informix.com>, Erik van Veen > <erikv@informix.com> writes > >Hi Thomas, > > > >minimize data movement as outlined in the IDS Performance Guide, and set > >the AFA_DSCANCHK environment variable prior to starting your 7.31 server. > > ?? What is this environment variable? > > -- > David Williams -- +-----------------------------------------------+ | Mark Christmas mailto:mchristmas@crosskeys.com | CrossKeys Systems Corp http://www.crosskeys.com | 350 Terry Fox Dr | Kanata, ON, Canada K2K 2W5 | Phone (613) 591-1600 Fax (613) 599-2330 +-----------------------------------------------+