Monolithic vs fragmented indices

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.

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.

"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. What you should monitor closely on YOUR data and on YOUR actual queries is the gain you get from readahead. Such gains are very different depending on the situation, but if you have advantage from monolithic idexes via read ahead in your situation and give this up detaching this indexes the difference can be tremendous, almost doubling the I/O load. If you then can not reduce I/O load by fragment avoidance ....... and so forth I found this out the other way round, by incorporating indexes into data dbspaces and increase read ahead on many occasions in the past Dick -- Richard Kofler debis Systemhaus Austria on a private account

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. 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. 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/

William Rice wrote in message <94crel$1nj$1@nnrp1.deja.com>... > >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. > Surely the possibility of fragment elimination within the index itself would be one of many possible reasons for choosing a particular strategy. I'd guess: If you have consciously fragmented a table so as to encourage fragment elimination - for example, to break out a few hundred or thousand "current and interesting" rows from a table with 100,000's or millions of rows, then you would be expecting most of your significant WHERE clauses to contain that fragment expression. If the index is similarly fragmented (I hope, but I think it's true) that the engine can also apply that fragment elimination to the index. Final consideration must be: if the index is actually useful! If the index has been thoughtfully designed, then that implies you have processes that will exploit that index. So, those processes will get the usual benefits of indexes - ie fast queries and/or ORDER BY clauses performed without the need for sorting or temporary indexes. This whole theory hinges around fragmentation strategies based on 95% + 5% approaches to fragmentation. Different principles would apply if you are chasing parallelism, which some people seem to have already discussed (I've only briefly glanced over their responses yet) What I'd really like to see from Informix is the ability, after applying a fragmentation strategy to an index, to then be able to say - well, if the row or the index key is in this fragment, don't even bother to store it. I can think of several big tables in our systems where an index entry for a row is only useful for a short while. Once the row ages, I'd like it to disappear from indexes and it seems entirely possible that partial indexes could be combined with fragmentation and still get the magic of 100% correct selects. It would also be nice to say - if the key is NULL, don't store it. The semantics of NULL would mean that the index could be correctly used if you said something like ... where ..... and keyfield = ? and .... The use of = immediately eliminates the possibility of matching NULL keys so therefore the index could be effectively used. Right now, if I want to implement a partial index, I have to emulate it with an associated table and triggers, and then the programmers must remember to filter thru the associated table if they want to achieve fast searches, and of course the searches cannot be as fast as a true index search anyway, unless there is massive elimination of NULLs or hysterical^H^H^H^H^H^H^H^H^H^H historical records.

I went ahead and decided to do some benchmarking. I can't seem to get the system I use to do a nested loop join using detached indexes. I was able to on the previous version. OPTCOMPIND is 0, I have tried with update statistics low drop distributions, update statistics medium, and update statistics high. Does anyone else have any ideas on what I can try.(My current version is 8.31, which does not support directives) Thanks, Will P.S. with an index which is not detached I can easily get nested loop joins to occur. 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/

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. > 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 if fragment elimination can be taken advantage of. Art S. Kagel > 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/