Re: Enough of the "DB2" thread, let's talk about Fragmented Tables

Colin Dawson schrieb: > With the use of SANs for storing the database how effective will the use of > fragmented tables be? I've got a few very large tables (16+ chunks) and was > considering splitting it into 6-8 fragments, they all have serial columns > and one has 7 indexes (or is it indeces). The last time I did anything real > with them was splitting a table across 4 disks, it improved the performance > no end. I'm not convinced I will get an improvement when using a SAN > Whenever you do a seqscan and you see (fragments all, parallel) you gain I/O parallelism on the input side and I/O speed up to the maximum. If you then add light scans, it really is hard to believe, how FAST things can be! Now, if you have 20 fragments sitting on 20 different LUNs, you have 20 access paths mapped to whatever your physical connection to the I/O subsytem is. This is one way to get the I/O speed which is possible (for a short time, that is, because it usually does not take too long until you are thru your I/O subsystems cache, which is always too small and then your are actually reading from your disks. Sadly those disks in todays average I/O subsystems are Year 2000 technology: VERY slow compared to SATA-2 and still more expensive by a factor of 12-15, at least in central Europe) I/O subsystems are definitely not the end of clever fragmentation, even not counting goodies like fragment avoidance, or index fragmentation. The latter means that you have to 'know your data' to the extreme, and maybe is not worth the hazzle, if we are not speaking about nrows in the range of 10**8 dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe