Re: Data Management Strategy?

That's where THE fragmentation comes into the picture. For such a monster table fragment by expression( how about one month each fragment? ) will help the database server to use fragment elimination for querying. This will improve the performence of query a lot. Also detaching the index on this table to use PDQ feature. And since the backup/restore can be done on the dbspace level, you can backup/restore data in a special data range. Purging the old data will become easier too because you only need to recycle the fragment instead of combing through all the data. If you denormalize the table and break it, all the applications/reports/forms will be recoded and tested. That involves a lot of work. But fragmentation is transparent to all the applications as long as they are not relying on rowid feature. HTH Dong >From: "Russell Bierschbach" <rbierschbach@simpletel.com> >Reply-To: "Russell Bierschbach" <rbierschbach@simpletel.com> >To: informix-list@iiug.org >Subject: Data Management Strategy? >Date: Mon, 29 Mar 1999 14:31:33 -0600 > >Is it common practice, or an exceptable one, to keep months (or years) worth >of history in a single table? I will attempt to give a general overview of >my current problem/situation, but I'm basically looking for an overall data >management strategy, or a reference to one. > >One table on our system is growing at around 500,000 rows per day, and is >expected to triple by the end of the year. Currently the row size for this >table is > 1K with > 80 fields (ouch). Needless to say this makes indexing >and querying the table very slow and difficult, especially ;when it's being >written to so frequently. The data in this table doesn't fit to a >master/detail table layout very well so breaking it down isn't a >possibility. I could split it into two tables, and have each table still be >somewhat useful, but I would have to include duplicate information in both, >thus making them take up more space. > >I am also faced with two rapidly approaching situations: > >1) I'm going to have to start archiving/deleting older data to make room for >new data. This poses a problem in that it's not easy to backup/recover >specific rows (date ranges) in one table. One solution "may" be to create >multiple copies of the same table, either one table per month, or maybe one >table per quarter (3 months), and put the records into the correct table >when they are first created. Doing so would keep the reporting guys busy >until the next century, and move me way to the top of their hit-list. > >2) Some of our customers are going to outgrow the systems they are using >now, and I'm going to have to migrate their data, and a large portion of >their history to new platforms. I've done this before, and it isn't very >fast, even using the high-performance loader. > > > > Get Your Private, Free Email at http://www.hotmail.com