Index strategy
A user on IDS 9.4 with a table fragmented by expression across 10 dbspaces asked whether a unique index should be fragmented alongside the table or placed in its own separate dbspace. Replies said it depends: fragment the index only if the scheme actually helps query times; a detached (non-fragmented) index costs ~4 extra bytes per entry to hold chunk/page info but can spread I/O load, while a global index makes ALTER FRAGMENT ATTACH/DETACH painful. To fragment a unique index you must include the fragmentation-expression column in the index key, which isn't always feasible. Advice was to benchmark; no single answer was settled on.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Scenario: IDS9.4 A table is fragmented (by expression) across 10 dbspaces. What is the best strategy for building a unique index? Should you create it like a regular non-unique index, that is, where it is created within the same dbspaces as the table. Or, perhaps it should be created in one separate, single dbspace, different from those used by the table and non-unique indexes. Thank you, Tony
Depends entirely on whether you can apply a fragmentation scheme to the index that will improve query times or not. Art S. Kagel ----- Original Message ----- From: Tony Demeis <ids@iiug.org> At: 8/14 11:59:02 Scenario: IDS9.4 A table is fragmented (by expression) across 10 dbspaces. What is the best strategy for building a unique index? Should you create it like a regular non-unique index, that is, where it is created within the same dbspaces as the table. Or, perhaps it should be created in one separate, single dbspace, different from those used by the table and non-unique indexes. Thank you, Tony ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
It all depends. If the index is detached, then you are carrying along an extra 4 bytes for every row pointed to, since the index now has to point to the chunk/page as well as the "rowid". If the disk(s) in question is/are fairly active, then splitting the index off from the data may reduce load. I'm sure you could think up a dozen other things as well. I would consider that a b-tree index only has a single point of entry, so you aren't speeding up index traversal by fragmenting it, although you may increase throughput (#simultaneous users) by fragmenting it. I would also consider the data fragmentation as well. If you have a non-fragmented index on a fragmented table, then doing an 'alter fragment... detach' will work great on your data, and then spend hours having to clean up the index. Considering all that, I would benchmark. My .02 j. >From: "Demeis, Tony (MOH)" <Tony.Demeis@ontario.ca> >Date: 2007/08/14 Tue AM 10:58:32 CDT >To: ids@iiug.org >Subject: Index strategy [9776] >Scenario: >IDS9.4 >A table is fragmented (by expression) across 10 dbspaces. > >What is the best strategy for building a unique index? Should you >create it like a regular non-unique index, that is, where it is created >within the same dbspaces as the table. Or, perhaps it should be created >in one separate, single dbspace, different from those used by the table >and non-unique indexes. > >Thank you, >Tony > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
This question has been there for a long time..... I think the primary purpose of fragmenting a table is to ease management! For example, you can load off the old data ( fragment) to have more space for the new coming data. If both data and index are fragmented, then it would be piece of cake to either take off or add on a fragment because all manipulations are localized( NO impact to other fragments or indexes !). The global index is really a contradiction to a fragmented table, it suffers when either add or subtract a fragment. Unfortunately we do not have a option here for the Unique index, do we? Thanks, Frank On 8/14/07, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote: > > It all depends. > > If the index is detached, then you are carrying along an extra 4 bytes for > every row pointed to, since the index now has to point to the chunk/page > as > well as the "rowid". If the disk(s) in question is/are fairly active, then > splitting the index off from the data may reduce load. I'm sure you could > think up a dozen other things as well. > > I would consider that a b-tree index only has a single point of entry, so > you > aren't speeding up index traversal by fragmenting it, although you may > increase throughput (#simultaneous users) by fragmenting it. I would also > consider the data fragmentation as well. If you have a non-fragmented > index on > a fragmented table, then doing an 'alter fragment... detach' will work > great > on your data, and then spend hours having to clean up the index. > > Considering all that, I would benchmark. > > My .02 > > j. > > >From: "Demeis, Tony (MOH)" <Tony.Demeis@ontario.ca> > >Date: 2007/08/14 Tue AM 10:58:32 CDT > >To: ids@iiug.org > >Subject: Index strategy [9776] > > >Scenario: > >IDS9.4 > >A table is fragmented (by expression) across 10 dbspaces. > > > >What is the best strategy for building a unique index? Should you > >create it like a regular non-unique index, that is, where it is created > >within the same dbspaces as the table. Or, perhaps it should be created > >in one separate, single dbspace, different from those used by the table > >and non-unique indexes. > > > >Thank you, > >Tony > > > > > > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
For a unique index, you would have to add the column involved in the fragment expression so that you could fragment the index. j. >From: FRANK <yunyaoqu@gmail.com> >Date: 2007/08/15 Wed AM 11:14:12 CDT >To: ids@iiug.org >Subject: Re: Index strategy [9789] >This question has been there for a long time..... > >I think the primary purpose of fragmenting a table is to ease management! >For example, you can load off the old data ( fragment) to have more space >for the new coming data. >If both data and index are fragmented, then it would be piece of cake to >either take off or add on a fragment because all manipulations are >localized( NO impact to other fragments or indexes !). > >The global index is really a contradiction to a fragmented table, it >suffers when either add or subtract a fragment. Unfortunately we do not >have a option here for the Unique index, do we? > >Thanks, >Frank > >On 8/14/07, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote: >> >> It all depends. >> >> If the index is detached, then you are carrying along an extra 4 bytes for >> every row pointed to, since the index now has to point to the chunk/page >> as >> well as the "rowid". If the disk(s) in question is/are fairly active, then >> splitting the index off from the data may reduce load. I'm sure you could >> think up a dozen other things as well. >> >> I would consider that a b-tree index only has a single point of entry, so >> you >> aren't speeding up index traversal by fragmenting it, although you may >> increase throughput (#simultaneous users) by fragmenting it. I would also >> consider the data fragmentation as well. If you have a non-fragmented >> index on >> a fragmented table, then doing an 'alter fragment... detach' will work >> great >> on your data, and then spend hours having to clean up the index. >> >> Considering all that, I would benchmark. >> >> My .02 >> >> j. >> >> >From: "Demeis, Tony (MOH)" <Tony.Demeis@ontario.ca> >> >Date: 2007/08/14 Tue AM 10:58:32 CDT >> >To: ids@iiug.org >> >Subject: Index strategy [9776] >> >> >Scenario: >> >IDS9.4 >> >A table is fragmented (by expression) across 10 dbspaces. >> > >> >What is the best strategy for building a unique index? Should you >> >create it like a regular non-unique index, that is, where it is created >> >within the same dbspaces as the table. Or, perhaps it should be created >> >in one separate, single dbspace, different from those used by the table >> >and non-unique indexes. >> > >> >Thank you, >> >Tony >> > >> > >> >> >> >>****************************************************************************** * >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> >> >******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Unfortunately, this is not always possible...... On 8/15/07, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote: > > For a unique index, you would have to add the column involved in the > fragment expression so that you could fragment the index. > > j. > > >From: FRANK <yunyaoqu@gmail.com> > >Date: 2007/08/15 Wed AM 11:14:12 CDT > >To: ids@iiug.org > >Subject: Re: Index strategy [9789] > > >This question has been there for a long time..... > > > >I think the primary purpose of fragmenting a table is to ease management! > >For example, you can load off the old data ( fragment) to have more space > >for the new coming data. > >If both data and index are fragmented, then it would be piece of cake to > >either take off or add on a fragment because all manipulations are > >localized( NO impact to other fragments or indexes !). > > > >The global index is really a contradiction to a fragmented table, it > >suffers when either add or subtract a fragment. Unfortunately we do not > >have a option here for the Unique index, do we? > > > >Thanks, > >Frank > > > >On 8/14/07, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote: > >> > >> It all depends. > >> > >> If the index is detached, then you are carrying along an extra 4 bytes > for > >> every row pointed to, since the index now has to point to the > chunk/page > >> as > >> well as the "rowid". If the disk(s) in question is/are fairly active, > then > >> splitting the index off from the data may reduce load. I'm sure you > could > >> think up a dozen other things as well. > >> > >> I would consider that a b-tree index only has a single point of entry, > so > >> you > >> aren't speeding up index traversal by fragmenting it, although you may > >> increase throughput (#simultaneous users) by fragmenting it. I would > also > >> consider the data fragmentation as well. If you have a non-fragmented > >> index on > >> a fragmented table, then doing an 'alter fragment... detach' will work > >> great > >> on your data, and then spend hours having to clean up the index. > >> > >> Considering all that, I would benchmark. > >> > >> My .02 > >> > >> j. > >> > >> >From: "Demeis, Tony (MOH)" <Tony.Demeis@ontario.ca> > >> >Date: 2007/08/14 Tue AM 10:58:32 CDT > >> >To: ids@iiug.org > >> >Subject: Index strategy [9776] > >> > >> >Scenario: > >> >IDS9.4 > >> >A table is fragmented (by expression) across 10 dbspaces. > >> > > >> >What is the best strategy for building a unique index? Should you > >> >create it like a regular non-unique index, that is, where it is > created > >> >within the same dbspaces as the table. Or, perhaps it should be > created > >> >in one separate, single dbspace, different from those used by the > table > >> >and non-unique indexes. > >> > > >> >Thank you, > >> >Tony > >> > > >> > > >> > >> > >> > > >>****************************************************************************** * > >> > Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > >> > >> > > >******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > >