RE: Index Fragmentation

If I recall correctly the index gets fragmented by the same strategy which the table does. So the index on b would be fragmented by a. It knows what fragment it is in when it updates, inserts or deletes the record, so it modifies the index at that point in time. When you do look ups, many times you have to do the look up on all of the fragmented indexes. 20 index lookups is usually slower than 1. For this reason, some people fragment a table, but not its indexes. Hope this helps, Will >===== Original Message From "Prasad Mahale" <gs0528@hotmail.com> ===== >Hi, > I have a performance issue with a fragmented tables/indexes. > > My question is how the attached index, which does'nt contain fragmented >key column, gets fragmented? What strategy it uses? What happens internally? > >for example - > >CREATE TABLE tb1(a int, b int) >FRAGMENT BY EXPRESSION >(a >=0 and a < 5) IN dbspace1, >(a >=5 and a < 10) IN dbspace2>... >; >CREATE INDEX idx1 ON tb1(a); >CREATE INDEX idx2 ON tb1(b);> >In the above example, index idx2 also gets fragmented across both dbspaces >whereas table is fragmented on column a. > >What strategy it uses to fragment such indexes? > >thanks in advance. > >Prasad Mahale >DBA Informix >pmahale@yahoo.com > > > > >________________________________________________________________________ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------