Using Fragmentation Strategy For Fast Deletes
All, We are on Informix 7.31 and Solaris 2.6. We've put together a fragmentation strategy (expression based) for a history table (15 - 20 Gig a year). The question that I have is can you issue and alter fragment statement and drop 1 of its dbspaces without having a place to migrate the data? In Redbrick, I can take a segment off-line and drop it when the data is out dated.. This keeps me from having to take the I/O hit on the delete. The SQL Syntax guide seemed to say that you couldn't drop a fragment unless its data could move somewhere else? Is this true or am I reading it wrong? I thought that Informix made a big deal about fast deletes using fragmentation strategies at one point in time?? Maybe that was XPS?? Thanks for your help, Michael Brizendine Pegasus Solutions Database Engineer
Michael Brizendine <michaelb@pegsinc.com> wrote in message news:8dfv8v$4kq$1@news.xmission.com... > > All, > > We are on Informix 7.31 and Solaris 2.6. We've put together a fragmentation > strategy (expression based) for a history table (15 - 20 Gig a year). > > The question that I have is can you issue and alter fragment statement and > drop 1 of its dbspaces without having a place to migrate the data? > > In Redbrick, I can take a segment off-line and drop it when the data is out > dated.. This keeps me from having to take the I/O hit on the delete. > > The SQL Syntax guide seemed to say that you couldn't drop a fragment unless > its data could move somewhere else? Is this true or am I reading it wrong? > I thought that Informix made a big deal about fast deletes using > fragmentation strategies at one point in time?? Maybe that was XPS?? In 7.x you can first detach fragment into the table, then drop table. -- ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285 > > Thanks for your help, > > Michael Brizendine > Pegasus Solutions > Database Engineer > >
Michael Brizendine wrote: > All, > > We are on Informix 7.31 and Solaris 2.6. We've put together a fragmentation > strategy (expression based) for a history table (15 - 20 Gig a year). > > The question that I have is can you issue and alter fragment statement and > drop 1 of its dbspaces without having a place to migrate the data? > > In Redbrick, I can take a segment off-line and drop it when the data is out > dated.. This keeps me from having to take the I/O hit on the delete. > > The SQL Syntax guide seemed to say that you couldn't drop a fragment unless > its data could move somewhere else? Is this true or am I reading it wrong? > I thought that Informix made a big deal about fast deletes using > fragmentation strategies at one point in time?? Maybe that was XPS?? Just detach the fragment, creating a new table made up of the one fragment, then you can drop the new table quickly. After 7.24 the detach is very quick, older versions tried to do an reorg bu twith 7.31 you will be OK. Check the manual for ALTER FRAGMENT syntax. Art S. Kagel