Re: fragmentation question

> ----- Original Message ----- > From: "" <tilleul17@web.de> > Sent: Wed, April 29, 2009 16:18 > Subject: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 > Floyd Wellershaus wrote: > Thanks ! > For the attached composite index does it matter which key comes first in > the index creation statement ? should the key that is the one the > fragmentation expression is based on come first ? > > Thanks again, > floyd It does matter. This depends on whether your application uses AN ORDER BY or GROUP BY clauses on the two fields. You should create it in a way that supports these queries. HTH Tilman

tilleul17@web.de wrote: >> ----- Original Message ----- >> From: "" <tilleul17@web.de> >> Sent: Wed, April 29, 2009 16:18 >> Subject: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 >> > Floyd Wellershaus wrote: > > Thanks ! > > For the attached composite index does it matter which key comes first in > > the index creation statement ? should the key that is the one the > > fragmentation expression is based on come first ? > > > > Thanks again, > > floyd > > > It does matter. This depends on whether your application uses AN ORDER > BY or GROUP BY clauses on the two fields. > You should create it in a way that supports these queries. > HTH > Tilman > Also take into consideration the cardinality (number of different values for the field) for each field. Assuming the relevant queries use all the fields in the key, the more selective ones should be used first. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...