RE: Data stored by fragments.

Oh dear, such a long topic. And according to the ICP exam one in which I am not truly an expert. If you have, for example, a table with a date in it. And every month you want to purge all records that are older than 6 months (or whatever). You can fragment - or split - the table onto different disks based on that date: create table foo....... date_col date......... fragment by expression month(date_col)=1 in dbs1, # where dbs1 is the name of a dbspace month(date_col)=2 in dbs2,...... (I suggest you check the syntax and not take the above (or the below for that matter) for gospel - I haven't used an expression for a while). When it comes time to purge a months worth of data, you can: ALTER FRAGMENT... DETACH... (you could just DROP, but I would archive it first from the new table you create with the detach). Clear off the disk and then re-attach the fragment as the next month. This means that you can do the purge in a matter of minutes instead of hours or days to delete each individual record (all the while leaving holes throughout your table and indices). The cost of this strategy is that your table is now optimized for date queries. If the users normally would query by customer_id, there will be no fragment elimination unless they include a date clause in their query. ----- I ran into code some time back where to do a yearly archive/purge the program opened a cursor against the entire table, read every row, checked them (within the program) for the date and then deleted them (the programmer forgot to write them somewhere else). A solution like that against a multi-million row table is asking for an execution time measured in weeks. With a fragmentation scheme as suggested you can literally do the work in minutes - alas it means that the task should not be automated, while you could write a program to figure out whcih fragment to drop and where to add the new one - it's the sort of program which will always have a problem somewhere and the consequences of it's failure would be a disaster. hope that helps. cheers j. > -----Original Message----- > From: Srinivas Sreekumar [mailto:SRINIVAS_SK@infy.com] > Sent: Monday, November 13, 2000 7:02 AM > To: informix-list@iiug.org > Subject: Data stored by fragments. > Importance: High > > > Hi Informix gurus, > > One of my developers has a requirement as below : > > We need to implement following requirement for our application, > > " TO SPEED UP THE PURGE PROCESS, IT IS SUGGESTED THAT DATA BE > STORED BY > FRAGMENTS AND THESE ARE TO BE DROPPED WHEN NOT REQUIRED. " > > Can anyone is the list enlighten me on how to implement this > ie, How to > store data in fragments and dropping the same fragments later on. > > > thanks in advance, > > Srini >

One thing to add, accessing records via a fragmented indexes can be a slowdown. Unless the index lookup can do fragment elimination, a lookup must be done on every fragment's index. If you have n fragments, it has to look at n indexes to find the matching records. So if you fragment by date, but are doing random access by the some other field, it requires more index reads to pull up a single record. So if this is a randomly accessed table, you might want to limit the number of fragments you break the table into. As a note, I am not sure how it handles unique/primary keys. It is possible it does index lookups till it finds the record in a fragment, and then does not do index lookups on the other fragments. Hope this helps, Will In article <8up42a$lb7$1@news.xmission.com>, "Parker, Jack" <JParker@engage.com> wrote: > > > Oh dear, such a long topic. And according to the ICP exam one in which I am > not truly an expert. > > If you have, for example, a table with a date in it. And every month you > want to purge all records that are older than 6 months (or whatever). You > can fragment - or split - the table onto different disks based on that date: > > create table foo.......> > date_col date......... > > fragment by expression > month(date_col)=1 in dbs1, # where dbs1 is the name of a dbspace > month(date_col)=2 in dbs2,...... > > (I suggest you check the syntax and not take the above (or the below for > that matter) for gospel - I haven't used an expression for a while). > > When it comes time to purge a months worth of data, you can: > > ALTER FRAGMENT... DETACH... > > (you could just DROP, but I would archive it first from the new table you > create > with the detach). > > Clear off the disk and then re-attach the fragment as the next month. > > This means that you can do the purge in a matter of minutes instead of hours > or days to delete each individual record (all the while leaving holes > throughout your table and indices). > > The cost of this strategy is that your table is now optimized for date > queries. If the users normally would query by customer_id, there will be no > fragment elimination unless they include a date clause in their query. > > ----- > > I ran into code some time back where to do a yearly archive/purge the > program opened a cursor against the entire table, read every row, checked > them (within the program) for the date and then deleted them (the programmer > forgot to write them somewhere else). A solution like that against a > multi-million row table is asking for an execution time measured in weeks. > With a fragmentation scheme as suggested you can literally do the work in > minutes - alas it means that the task should not be automated, while you > could write a program to figure out whcih fragment to drop and where to add > the new one - it's the sort of program which will always have a problem > somewhere and the consequences of it's failure would be a disaster. > > hope that helps. > > cheers > j. > > > -----Original Message----- > > From: Srinivas Sreekumar [mailto:SRINIVAS_SK@infy.com] > > Sent: Monday, November 13, 2000 7:02 AM > > To: informix-list@iiug.org > > Subject: Data stored by fragments. > > Importance: High > > > > > > Hi Informix gurus, > > > > One of my developers has a requirement as below : > > > > We need to implement following requirement for our application, > > > > " TO SPEED UP THE PURGE PROCESS, IT IS SUGGESTED THAT DATA BE > > STORED BY > > FRAGMENTS AND THESE ARE TO BE DROPPED WHEN NOT REQUIRED. " > > > > Can anyone is the list enlighten me on how to implement this > > ie, How to > > store data in fragments and dropping the same fragments later on. > > > > > > thanks in advance, > > > > Srini > > > Sent via Deja.com http://www.deja.com/ Before you buy.