Summaries to Large Table Management

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