fragmented indexes?

v11.50.fc5 Aix 6.1 A general question... We have a table that will be fragmented by a fiscal week id, ie. 201101 ... We are also going to fragment the index. If the index is also fragmented by fiscal week, is it best to have that as part of the index column list. Obviously it doesn't have to be included, but I want to know what is the "best practice"... thanks for any help ... Peter Logan Senior Database Administrator Phone: 616/878-8309

If fiscal week will be one of the filters in most queries that are likely to access that index key, then I would include that column in the key, yes. Obviously there isn't much value to making it the leading column, but including it may reduce the number of data pages that will be examined when fragment elimination isn't possible. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Wed, Jun 9, 2010 at 12:08 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > v11.50.fc5 > Aix 6.1 > > A general question... We have a table that will be fragmented by a fiscal > week id, ie. 201101 ... We are also going to fragment the index. If the > index is also fragmented by fiscal week, is it best to have that as part > of the index column list. Obviously it doesn't have to be included, but I > want to know what is the "best practice"... > > thanks for any help ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd5d012b1007c0488964fa6

Thanks Art .. one follow-up ... If the fiscal week isn't part of the key ... and I want to detach old fragments ... Will that work .. meaning be almost instantly .. or would it then be like having a non fragmented index on the table ..... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 06/09/2010 06:25 AM Subject: Re: fragmented indexes? [20335] Sent by: ids-bounces@iiug.org If fiscal week will be one of the filters in most queries that are likely to access that index key, then I would include that column in the key, yes. Obviously there isn't much value to making it the leading column, but including it may reduce the number of data pages that will be examined when fragment elimination isn't possible. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Wed, Jun 9, 2010 at 12:08 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > v11.50.fc5 > Aix 6.1 > > A general question... We have a table that will be fragmented by a fiscal > week id, ie. 201101 ... We are also going to fragment the index. If the > index is also fragmented by fiscal week, is it best to have that as part > of the index column list. Obviously it doesn't have to be included, but I > want to know what is the "best practice"... > > thanks for any help ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd5d012b1007c0488964fa6 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Whether the column is in the key shouldn't affect fragment detach. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Wed, Jun 9, 2010 at 7:27 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Thanks Art .. one follow-up ... If the fiscal week isn't part of the key > .... and I want to detach old fragments ... Will that work .. meaning be > almost instantly .. or would it then be like having a non fragmented index > on the table ..... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "Art Kagel" <art.kagel@gmail.com> > To: > ids@iiug.org > Date: > 06/09/2010 06:25 AM > Subject: > Re: fragmented indexes? [20335] > Sent by: > ids-bounces@iiug.org > > If fiscal week will be one of the filters in most queries that are likely > to > access that index key, then I would include that column in the key, yes. > Obviously there isn't much value to making it the leading column, but > including it may reduce the number of data pages that will be examined > when > fragment elimination isn't possible. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > 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 Wed, Jun 9, 2010 at 12:08 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > v11.50.fc5 > > Aix 6.1 > > > > A general question... We have a table that will be fragmented by a > fiscal > > week id, ie. 201101 ... We are also going to fragment the index. If the > > index is also fragmented by fiscal week, is it best to have that as part > > > of the index column list. Obviously it doesn't have to be included, but > I > > want to know what is the "best practice"... > > > > thanks for any help ... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --000e0cd5d012b1007c0488964fa6 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cdf1866cd069404889a2a11