Re: To fragment or not to fragment - opinions requested (fwd)

Probably the best way to describe how to proceede is "your actual milage may vary". It is important to remember that the optimizer depends upon the distributions more in 7.10 than in previous versions. I have had queries go from minutes to hours when they did not have distributions. Once the distributions were created, the query performed better than it had before. Another thing to remember is that this is the first go-round with fragmentation and pdq, so there is bound to be many bugs and they should be fixed in 7.11. } } we tried round robin on our large dss and it failed miserably. for example } a query took 6+ hours to run. i then created 1 large dbspace for the given } table (the indexes are in a separate dbspace) and the query ran in 12 minutes. } the fragments were on 12 separate drives distributed accross 4 controllers } on a 4 way sun sparc center 2000. round robin failed miserably for our 10 } million row, 3 index, 8 column, 103 byte row table. i have my ideas on why } round robin should not be used, but thats a different story.... } } > } > Not to contridict what the trainers tell you, but the fragmentation } > scheme depends upon the nature of your business. round robin works } > well for some oltp applications as well as some dss applications. It } > is best when you do not know the distributions of your data or you want } > to achieve the best random i/o possible. expression based is good } > for when you know the distributions of your data. Any fragmentation } > scheme needs to have someone to watch it periodically so that it } > does not fall out of touch with your data. } > } > > } > > The approach recommended in the Informix DBA Class was not to use round } > > robin but fragmentation "BY EXPRESSION". This should be done across DBSPACE } > > to have a balance in the data. } > > } > > On Thu, 14 Sep 1995, Robert D. Miles wrote: } > > } > > } On a SMP (4 processors) system is it better to fragment all of the tables } > > } using round robin, or should the tables be put is different or the same } > > } Dbspace? My main concern is for performance on a database that is normalized } > > } and uses several joins on queries. Yes we will be changing the design to } > > } de-normalize some of the larger tables as new software is developed. } > > } } > > } Currently the database has about 150 million rows and is growing. } > > } } > > } An input on the trade offs of using fragmentation or not will be appreciated. } > > } } > > } } > > } Bob } > > } } > > } Informix version 7.10 } > > } } > > } > } > } > -- } > } > Dave Proksch - DBA - ValueRx - } > Phone: (810) 333-8622 Fax: (810) 253-6510 } > email: dproksch@vrx.vhi.com } > snail mail: 1825 South Woodward Suite 200 } > Bloomfield Hills, MI 48302 USA } > } > } > #include <std_disclaimer.h> } > } } } -- } regards, } +----------------------------------------------------------------------------+ } | . . | Bob Baskett | } | ... ... | Software Engineer | } | ..... ..... | Business Systems Integration Group | } | .. ... .. | Semiconductor Products Sector | } | . . . | Mesa, AZ | } | | President, Informix Users Group Of Arizona | } | Motorola, Inc. | rzbj40@email.sps.mot.com | } +----------------------------------------------------------------------------+ } -- Dave Proksch - DBA - ValueRx - Phone: (810) 333-8622 Fax: (810) 253-6510 email: dproksch@vrx.vhi.com snail mail: 1825 South Woodward Suite 200 Bloomfield Hills, MI 48302 USA #include <std_disclaimer.h>