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

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 | +----------------------------------------------------------------------------+