question about fragment

Hi, I have one table need to be fragmented, totally it's 8 partitions,I have two choises,the first one is all those partition on the same dbspace, the second one is use 8 different dbspace to do the fragment. What's the performance difference between them? thanks for your time.

It depends. If you are going to access all 8 partitions in parallel, = then there is benefit to spreading them out across disks (hence = dbspaces). If the fragmentation is date based and you will only really = be hitting one of them, then there is limited point. =20 Reasons for fragmentation: Improve parallelism, as each fragment gets it's own scan thread. = You get this benefit if you are reading a lot of data from the table in = question (DSS). If you are going after onesy twosey rows (OLTP), then = there is not much benefit. Data management, you want to quickly purge old data by dropping a = fragment and adding a new one. This typically means the the current = fragment is getting the heavy use and there would then be limited point = to spreading out across disks. Fragment elimination. You want to reduce the amount of data scanned = by some filter to only one or two fragments. Again, limited point to = spreading across disks. Your mileage will vary. j. On Jan 9, 2012, at 8:55 PM, CHUAN LU wrote: > Hi,=20 >=20 > I have one table need to be fragmented, totally it's 8 partitions,I = have two=20 > choises,the first one is all those partition on the same dbspace, the = second=20 > one is use 8 different dbspace to do the fragment. What's the = performance=20 > difference between them? thanks for your time.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

If all 8 dbspaces are built from the same physical device then there will likely be little difference. If the 8 dbspaces are built from 8 different disks or physical disk arrays then this will be 8 times faster when stressed, especially if the 8 disks/arrays are connected over separate channels. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jan 9, 2012 at 8:55 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > I have one table need to be fragmented, totally it's 8 partitions,I have > two > choises,the first one is all those partition on the same dbspace, the > second > one is use 8 different dbspace to do the fragment. What's the performance > difference between them? thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f235589f95cdb04b62363ed