Forwarded mail...

Forwarded message: } From uucp@attmail.com Wed Nov 10 21:57 EST 1993 } Message-Id: <9311101353.AA19742@hp.com> } From: uucp@attmail.com } Date: 10 Nov 93 13:50:11 GMT } To: cynthia@hpsgcip1.sgp.hp.com } Report-Version: 3 } Original-Date: Wed Nov 10 13:41:57 EST 1993 } Original-Subject: Summaries to Large Table Management } Confirming-Mts-Message-Id: <internet3141350110> } Not-Delivered-To: !rstorer due to code 01 "Invalid Address Specification"; } Supplementary-info " Recipient not found." } Content-Type: message } } >From rmy.emory.edu!ilist Wed Nov 10 13:41:57 EST 1993 remote from internet } Received: by att.att.com; Wed Nov 10 08:48:25 EST 1993 } Received: by } rmy.rmy.emory.edu (5.65/Emory_rmy.3.4.0) via MAILPROG } id AA26332 ; Wed, 10 Nov 93 08:52:05 -0500 } Return-Path: ilist@rmy.emory.edu } From: internet!hpsgcip1.sgp.hp.com!cynthia (Cynthia Leong) } Message-Id: <9311101341.AA08563@hpsgcip1.sgp.hp.com> } Subject: Summaries to Large Table Management } To: internet!rmy.emory.edu!informix-list } Date: Wed, 10 Nov 93 13:41:57 EST } X-Mailer: ELM [version 2.2 PL14] } Sender: internet!rmy.emory.edu!informix-list-owner } Reply-To: internet!hpsgcip1.sgp.hp.com!cynthia (Cynthia Leong) } X-Informix-List-To: rstorer@attmail.com } X-Informix-List-Id: <list.3075> } } Hi, } } Thanks to those who have contributed to the discussion of } Large Table Management. Through these contributions, I have } finally decided on a method of handling tables that grows } at a rate of 25,000 rows/day. } } Here's the proposal : } } (when due) } production raw data ----------> archived data -------------> deleted data } (Fast hardisk) (daily) (optical disk or other slower disk) } \\ ^ } \\ | } \\ | age-off data } \\ | } \\ | } -------> current year's data } (summarized) (Fast hardisk) } } Comments : } } Only those raw data that cannot be summarized will be directly transfer } to the archive data storage area and those that can be summarized will } normaly be transfered to the current year's data storage. Usually, the } non-summarizable data are seldom in demand unlike the summarised data. } } Age-off data, ie data that have passed its useful period will be transfered } to the archived data storage and become historical data. These historical } data will be deleted/removed once end-of-its-lifecycle has come. } } Tables will not be split no matter how large it grows. } } The contraints to ensure acceptable level of performance: } 1) report-generation/ data manipulation at the archived data will be } slow and user need to understand this fact. } Low performance is due to : } - table may be very large } - table reside on slower disk } 2) indexes that matches the method of accessing are available } } The constraints to ensure maintainability of the above method : } 1) reliable utilities/tools for data transer automatically is available } (eg. cron , database utilities etc) } 2) hardware used for storage is easy to replace. } 3) recovery plan is in place in case of unavoidable disaster. } } --------------------------------------------------------------------- } } Summary of contributions: } } Cathy Kipp } Keep the current & old info in the same table. The key to performance } - use keys eg date + unique field, } status + unique field } Spliting tables complicates programming and maintenance } } Jack Parker } option 1 : back data off to another table for backup } option 2 : back off & summarize } option 3 : achive & restore when requested ( calls for some codes) } option 4 : back off to different engine, requests for old data go } against that engine and everybody understands that } performance is not peak } } Blair Elzinga } Backing up data to another system via cron. 2 problems encountered: } 1) backup system still grows without bound } 2) problems with crons } } Alternatively, have a system architecture that backup the data in real } time. } } Spliting tables into months is depended on whether the method of access } is date-oriented. If not, there will be increase in complexity of report } writing. } } Leave table intact and use index that match method of accessing. } } ------------------------------------------------------------------------ } } The End. I hope you have enjoyed reading. } } Cheers, } Cynthia Leong } Hewlett-Packard, Singapore } cynthia@hpsgcip1.sgp.hp.com } }