Index extents after defragging tables

[mjldba]

I have a question about index extents.

Just did a dbexport to defrag a DB, I modified the initial extent/next extent numbers for tables where needed, and dbimported.

I got the following info using oncheck -pT.

The table I'm using as an example has all data pages in the 1st extent (extent size 82837, next size 8283) as I would expect but the initial extent size for all indexes ranges from 757 to 1818 and next extent size ranges from 7 to 18.
Some indexes have 60+ extents right after defragging.

I know that extents created contiguously are treated as one extent but where are the initial extent/next extent #'s for index extents coming from? I thought these numbers would be inherited from the table but they're not so I'm confused.

Any index extents (using next extent size) created later due to database activity will be very small & the indexes will become heavily fragmented quickly with these very small next extent sizes.

Is it the method I'm using to determine initial extent and next extent sizing? Am I missing something?

Thanks,

Mike

 

[bjhogan]

The index extent size is inherited from the table. It is based on a ratio between row size and index key size. So it would be hard to determine the extent sizing for the indexes(unless you could peek at the informix source code). But, even if you could determine it, it really wouldn't do you much good. The only thing you can do to ensure that your indexes do not become 'fragmented' across the dbspace is to create an index in it's own dbspace.

create index index_i01 on table_1(col1, col2) in table_dbspace

One thing you might do is pull an extent report(oncheck -pe) to see how the indexes are fragmented across the dbspace.


Hope this helps..

--Ben

 

[mjldba]

Hi Ben,

Thanks for responding, I'll take a look at how the indexes are fragmented across their dedicated dbspace. The indexes are detached from the data by locating them in a separate dbspace 'cause I read that would enhance performance a bit & increase efficiency.

Regards - Mike