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

Colin, If you fragment by expression, you can gain a huge performance boost by making use of fragment elimination. Check out the performance guide for more details. The basic principal is if queries contain filters that match how you've fragmented your data by expression, Informix will not scan the dbspaces that do not contain the data you are looking for. We have fragmented a large table into 366 dbspaces, each dbspace contains one day (Feb 23rd for example), if a query wants to find data for only one day, Informix ignores the 365 dbspaces that are guaranteed not to contain the data we are looking for. Throw in some pdq to scan multiple fragments in parallel and light scans to bypass the buffer pool and we can run a report over multiple days on a 1 TB table in 10 minutes. This is super powerful and by far one of the best performance tuning projects we've completed. You can fragment your indexes as well, but the benefits will depend on how your data looks. We keep ours in their own dbspace with no fragmentation. There are other benefits to fragmentation as well, detaching and attaching fragments to quickly delete and load large amounts of data come to mind. Andrew "Colin Dawson" <cjd_1955@hotmail.com> wrote in message news:1122561967.47074756ec1baabe5e50e6d872905f6a@teranews... > > > > 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 > > > > Regards > > Colin > > > There are 10 types of people in the world, those that understand binary and > those that don't > > > sending to informix-list