Re: Monolithic vs fragmented indices

Seems "it always depends"... William Rice wrote: > Fragmenting based on the columns which are in the index is better than > a single dbspace index for large tables. The two questions which seem > fuzzier for me are the following: > 1. At what point is it worthwhile to create this index different than > the table fragmentation, due to some of the limitations this causes. If the index is part of a unique constraint and that index is fragmented, then all of the index fragments must be examined with a single insert. If the index is fragmented by expression and the data is located within the index, then index fragment elimination can be utilized to reduce this overhead. As a 'wild guess' I'd suspect that if the ratio of inserts to reads for a table exceeds the number of index fragments that there would be an issue. For instance if there are 10 index fragments, and index fragment elimination can not be used, and this is a unique index, then if the percentage of inserts to reads against the table exceeds 10%, then there would be additional overhead. I suspect that the most common issue is that folks will want to fragment for parallel scan reasons and simply use round-robin. Then they create all kinds of constraint indexes... Hummm --- no index fragment elimination.... Opps --- poor performance with inserts.... > > 2. Is it ever worthwile to have a index fragmented the same way as the > table if the table is not fragmented by any of the columns in the index. Yes - Some folks want to be able to do a quick detach/attach fragment to large tables. > > > I think both these situations require benchmarks which I haven't > performed yet. > > Will > > In article <3A6877F9.8F5B3390@home.com>, > Madison Pruet <mpruet@home.com> wrote: > > If the index is not a unique index and the number of rows is rather > large, > > fragmentation is probably a good idea. If the index is a unique (or > primary > > key) index, then index fragmentation is probably a bad idea (unless > it is > > fragmented by expression and the expression can be resolved by the > data in the > > index). > > > > "Parker, Jack" wrote: > > > > > William and I have been having a nice quiet back-channel chat about > indices. > > > Something which arose from that was a question about whether it was > better > > > to fragment an index or have it on one dbspace. The advantage of > the 1 > > > dbspace is you only have one thread hitting the index whenever > there is a > > > lookup, the advantage of the fragmented index is multiple threads > (which is > > > also a disadvantage when you have 'n' hundred users) and the > marginally > > > shallower btree. I had thought that a cow-orker had benchmarked > this sort > > > of thing and find that he doesn't really recall. His answer is: > > > > > > 'it depends on the size of the index, big indices (aside > from being > > > limited to 32GB/dbspace) can profit, > > > while smaller ones (<2GB) might not'. > > > > > > Does anybody else have light (or water) to shed on this discussion? > > > Preferably with some numbers to back them? > > > > > > cheers > > > j. > > > > > > Sent via Deja.com > http://www.deja.com/

Thank you very much for your response. Unfortanately, due to my wording, the question you answered was not the one I was trying to ask. I am well aware of the benefits of fragmenting and index by it's own key vs by some other attribute in the table. Do note, we are discussing indexes on attributes of a table, not primary key's or constraints of any type. A little history on the discussion. We were discussing the point in time at which adding an index becomes worthwile. I have done some experimentation with indexes fragmented by the same algorithm as the table, as well as a detached index in a single dbspace, I had not thought to use an index fragmented by it's own key until Jack brought it up... I did some testing with a table with had 20 fragments, and from what I saw an index fragmented by the same strategy as the table is pretty useless unless you plan on selecting an _extremely_ small subset of the data(unfortanately I can't find any of my numbers on this test). With the detached index in 1 dbspace, getting 1% of the data was equally fast as a hash join. I can't imagine a fragmented index giving me that much of a performance boost on selects, but will have to do some benchmarking. In a previous post you mentioned the characteristics of the data could change the behaviour, so I will have to add this to my tests. My restated questions. 1. At what point is an index fragmented by it's own key, going to give me better performance than a hash join. 2. At what point is an index fragmented by the tables fragmentation strategy, going to give me better performance than a hash join. Thanks for your response, Will In article <3A6A05B8.EA4B5FDF@home.com>, Madison Pruet <mpruet@home.com> wrote: > Seems "it always depends"... > > William Rice wrote: > > > Fragmenting based on the columns which are in the index is better than > > a single dbspace index for large tables. The two questions which seem > > fuzzier for me are the following: > > 1. At what point is it worthwhile to create this index different than > > the table fragmentation, due to some of the limitations this causes. > > If the index is part of a unique constraint and that index is fragmented, > then all of the index fragments must be examined with a single insert. If > the index is fragmented by expression and the data is located within the > index, then index fragment elimination can be utilized to reduce this > overhead. As a 'wild guess' I'd suspect that if the ratio of inserts to > reads for a table exceeds the number of index fragments that there would be > an issue. For instance if there are 10 index fragments, and index fragment > elimination can not be used, and this is a unique index, then if the > percentage of inserts to reads against the table exceeds 10%, then there > would be additional overhead. > > I suspect that the most common issue is that folks will want to fragment for > parallel scan reasons and simply use round-robin. Then they create all > kinds of constraint indexes... Hummm --- no index fragment elimination.... > Opps --- poor performance with inserts.... > > > > > 2. Is it ever worthwile to have a index fragmented the same way as the > > table if the table is not fragmented by any of the columns in the index. > > Yes - Some folks want to be able to do a quick detach/attach fragment to > large tables. > > > > > > > I think both these situations require benchmarks which I haven't > > performed yet. > > > > Will > > > > In article <3A6877F9.8F5B3390@home.com>, > > Madison Pruet <mpruet@home.com> wrote: > > > If the index is not a unique index and the number of rows is rather > > large, > > > fragmentation is probably a good idea. If the index is a unique (or > > primary > > > key) index, then index fragmentation is probably a bad idea (unless > > it is > > > fragmented by expression and the expression can be resolved by the > > data in the > > > index). > > > > > > "Parker, Jack" wrote: > > > > > > > William and I have been having a nice quiet back-channel chat about > > indices. > > > > Something which arose from that was a question about whether it was > > better > > > > to fragment an index or have it on one dbspace. The advantage of > > the 1 > > > > dbspace is you only have one thread hitting the index whenever > > there is a > > > > lookup, the advantage of the fragmented index is multiple threads > > (which is > > > > also a disadvantage when you have 'n' hundred users) and the > > marginally > > > > shallower btree. I had thought that a cow-orker had benchmarked > > this sort > > > > of thing and find that he doesn't really recall. His answer is: > > > > > > > > 'it depends on the size of the index, big indices (aside > > from being > > > > limited to 32GB/dbspace) can profit, > > > > while smaller ones (<2GB) might not'. > > > > > > > > Does anybody else have light (or water) to shed on this discussion? > > > > Preferably with some numbers to back them? > > > > > > > > cheers > > > > j. > > > > > > > > > > Sent via Deja.com > > http://www.deja.com/ > > Sent via Deja.com http://www.deja.com/