Re: fragmentation question

Floyd Wellershaus wrote: > > Running IDS10.0.uc5 on aix5.3. > We have a table that has some bad queries written against it that are having poor performance now. It is also a pretty wide table, lots of varchars. > We aren't going to re-write the queries at this minute, but instead I am going to fragment the table. > Investigating fragmenting it on the date_notified field. > > My question is, that even though I know the queries that filter on date_notified will work much quicker because they can do fragment elimination, I'm sure other queries will suffer, especially if I leave the indexes alone which will cause them to go into the data fragments. > > Is it best in this circumstance to just put the indexes in a separate unfragmented dbspace ? > > They have one composite index with date_notified. It is some other column and date_notified. I am thinking to drop that index, make one that is just date_notified and have that one follow the fragmentation scheme of the table. > Then create a new index for the other column of the index I just dropped. That, and the rest of the indexes to go in a separate dbspace all together. > > Is this how it's done or are there any suggestions ? > > Thanks, > Floyd If you fragment the table by expression on date_notofied, you should create indexes as follows: 1) if the indexes contain the fragmentation field, i.e. "date_notified", then you may create them as attached indexes or as deatched indexes. Based on the assumption that paralleism (PDQPRIORITY ! ) may speed up your queries, creating the indexes attached seems to be a good idea. I don't understand why you would want to drop the other column from the index that contains 'date_notified' , this does seem unnecessary (and probably a bad idea depending on the queries) . 2) if the indexes do NOT contain the fragmentation criteria , you should create them as detached indexes. HTH Tilman