Table fragmentation

Hi all. We're preparing to roll to a new version of our application software. The process will entail creating a copy of our current database for them to use as they install/test the new software. This seems like an opportunity to make some adjustments. Are there any good/bad experiences in fragmenting tables? We're a university, and I'm wondering if it makes sense to fragment on expression a few of our largest tables such that current students (those that will be 'hit' the most) are in a separate dbspace from all the others? This would seem to reduce the number of records in a given dbspace for those that are accessed most frequently. Or does it make more sense to spread all the records out (round-robin) amongst all the dbspaces? The dbspaces are in a RAID5, so does fragmenting tables that are stored in a RAID5 disk system even make sense? Any advice on other things I can do while I have the opportunity of rebuilding things from scratch are certainly welcome! (I plan already on fixing some extents that are *way* out of whack). Thanks, Brian McLaughlin Administrative Computing George Fox University (503) 554-2587

Brian, First off, be prepared for Art's response against Raid 5! I'm sure it's coming.... :-) Even though Raid 5 stripes across the entire disk array, one benefit of fragmenting your tables is Fragment Elimination. Keeping that in mind, you can devise a fragmentation scheme which should help optimize your queries. Not knowing the ins and outs of the app., it's hard to say what the best scheme would be, but it definitely sounds like splitting Alumni out from current students is a start. You might even want to split classes into separate fragments, making it easier later to roll them into the larger Alumni dbspace as they progress. Glad you mentioned Reorging your tables to take care of extraneous extents. Always a major improvement for efficiency! Good Luck! Michael Hoffman