detached indexes for fragmented data
I'm working with an application that runs on IDS 7.31 UD4 on AIX 4.3.3 and uses IBM's ESS SAN technology. When the SAN was rolled in two years ago, we saw a huge drop in the checkpoint times, but the app performance did not speed up much. Our database is badly interleaved and we are working towards a reorg of the data. Our reorg strategy tries to combine ease of administration with some performance benefits. We are moving the larger tables to their own dbspaces and plan to place indexes in a separate dbspace. We have a 25M row order_transaction table that we would like to fragment, not by its primary key, (id), which is not used by many queries, but instead by order_number, which is used by many queries and jobs. Both order_number and id are sequential, so growth would be fairly easy to manage (just add another fragment by order_number when the current one is getting full). Recent orders get the most activity, from application requests and reports, but no real DSS activity, since we have a data warehouse on an Oracle database. For the order_transaction table, we are hoping to get some "fragment elimination" (phrase from the Performance Guide) for queries using order_number by using order_number in the frag. expression. My question is: will IDS still bypass the non-relevant fragments if we have one detached index (in the index dbspace) that contains order_number values for all fragments? Or would it be more efficient to let the order_number index be implicitly attached to each fragment, instead of the one big index? Or, third option, create an order_number index that is fragmented by order_number, but stored in other dbspaces. The Performance Guide doesn't say much on the effect of different index strategies on "fragment elimination". Unfortunately, we have a test server for about one more week, so we can't do much benchmarking using different approaches. Pete Link University of Michigan Health System MCIT/Pharmacy Systems
On Thu, 15 Jul 2004 17:05:54 -0400, Peter Link wrote: Option three is most likely to give optimum performance as fragment elimination will apply both to the table and the index itself. Note that the most important thing to trigger fragment elimination is PDQPRIORITY > 0. PDQPRIORITY == 1 triggers fragment elimination but minimal parallelism. PDQPRIORITY >= 2 triggers fragmentation elimination and parallel query processing including parallel fragment scanning when multiple fragments have to be searched. Next most important is proper data distributions so make sure your UPDATE STATISTICS procedures are up to snuff. Art S. Kagel > I'm working with an application that runs on IDS 7.31 UD4 on AIX 4.3.3 and > uses IBM's ESS SAN technology. When the SAN was rolled in two years ago, we > saw a huge drop in the checkpoint times, but the app performance did not > speed up much. > > Our database is badly interleaved and we are working towards a reorg of the > data. Our reorg strategy tries to combine ease of administration with some > performance benefits. We are moving the larger tables to their own dbspaces > and plan to place indexes in a separate dbspace. > > We have a 25M row order_transaction table that we would like to fragment, > not by its primary key, (id), which is not used by many queries, but instead > by order_number, which is used by many queries and jobs. Both order_number > and id are sequential, so growth would be fairly easy to manage (just add > another fragment by order_number when the current one is getting full). > Recent orders get the most activity, from application requests and reports, > but no real DSS activity, since we have a data warehouse on an Oracle > database. > > For the order_transaction table, we are hoping to get some "fragment > elimination" (phrase from the Performance Guide) for queries using > order_number by using order_number in the frag. expression. > > My question is: will IDS still bypass the non-relevant fragments if we have > one detached index (in the index dbspace) that contains order_number values > for all fragments? > > Or would it be more efficient to let the order_number index be implicitly > attached to each fragment, instead of the one big index? > > Or, third option, create an order_number index that is fragmented by > order_number, but stored in other dbspaces. > > The Performance Guide doesn't say much on the effect of different index > strategies on "fragment elimination". > > Unfortunately, we have a test server for about one more week, so we can't do > much benchmarking using different approaches. > > > Pete Link > University of Michigan Health System > MCIT/Pharmacy Systems