Re: indexes and extents

> > I am using informix 7.13.UC2 on an hpux 10.01. I have created 4 > > indexes in a separate dbspace for this huge table that I have. What I > > have found is that when I used the 'create index idxname on > > tblname(col1) in dbspace', i found that the index has an entry in the > > smi tables. > > > > I could not control the extent size when I created the index, unlike > > creating table, and these indexes have quite a number of extents to > > them. > > > > Is there a way of giving the index an initial extent size, and > > prevent or reduce the number of extents on that particular index. > > > > The first / next extent size of the index is dependent on the first / > extent size of the table. To my knowledge, there is no way to reset > extent sizes on detached indices. > Not exactly. The first extent of the index depends on the number of pages allocated to the table at the time the CREATE INDEX statement is run. The engine will try to allocate a single extent large enough to hold all of the keys that could fit into however many pages there are in the table at that time, regardless of whether the pages have data or not. John is correct in that if you CREATE TABLE with extent size 2000000, then CREATE INDEX, it will try to allocate enough index pages to hold whatever keys could be stored in 2000000 KB of table. Conversely, if you CREATE TABLE with extent size of 16 KB, then CREATE INDEX, the index will be much smaller. If you then insert 500000 rows into this small table, then DROP INDEX and CREATE INDEX, the first extent of the index will be much larger now, as it is calculating index requirements based on all of the allocated extents in the table rather than just the first extent size of the table. At least, that's the behavior I've witnessed on 7.10.UC3 and 7.14.UC1. Haven't had a chance to test it on 7.23.UC2 yet. That makes me wonder why the original poster has multiple extents. Has there been a lot of insert activity on the table after the indexes were created? If so, try DROP INDEX/CREATE INDEX to rebuild them. Or are there multiple small chunks in the dbspace that contains the indexes? Remember that an extent can not span chunks, so that can cause multiple extents. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.