Re: Fragmentation opinion wanted.

Chuck Renaud wrote: > > > No. There is one aim with fragmentation (partitioning) and that is to > > balance IO. That does not always include fragment elimination. If you > > are loading data, then round robin will give you a better balance of IO > > than expression based. If you have a lot of users attacking various > > records, then expression based may well give a better balance of IO than > > round robin. > > I probably should have been more verbose. > > Balancing I/O is only a part of it...the aim of fragmentation is > efficient data access. I/O balancing by itself doesn't mean > anything if you're hitting 'X' million rows unnecessarily. Right. > The physical goal of fragmentation is to achieve parallelism and/or > fragment elimination, preferably both. The end result more efficient > access to data via balanced I/O and/or exclusion of irrelevant data. > > I also think we're venturing into two different subject areas and > that's: > > Benefits of fragmentation during loading > > vs. > > Benefits of fragmentation for data access Well I guess I should have been more verbose as well. Loading was just an example of data access where fragmentation elimination could hurt performance. But consider a data warehouse, reading a large percentage of a large table. You want to hit as many spindles as possible in order to maximise performance. > I'd be surprised to see much in common between these two, > though that obviously depends on the data content. If you're > fragmenting by round-robin, then you're probably shooting for great > load performance. Expression/hash fragmentation is going to > impact load time, but should clean house on queries. Forget the loading, that was just an example. :-) It depends on your queries. Assuming that fragment elimination will help performance is a generalisation that has caught many people out. Pushing a large number of threads onto a single disk, rather than spreading them out over many can hurt performance, depending on the type of operation. There, is that verbose enough? :-) And I guess you could generalise now that fragment elimination is good for OLTP and bad for DSS. ;-) Well, I suppose that's closer to it for most sites, but not all. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+