Re: How is disk space allocated to Indexes?

apatankar@s1.com wrote: > > My questions are as follows: > Assume that you create a table and allocate it an initial extent of some size. > Then you create an index on that table in the same dbspace. Then you add rows > to the table, so that nodes get created in the index. Is the space allocated > to the index part of the initial extent allocated to the table, or would a > separate extent be allocated to the index? If a separate extent is allocated > to the index, then what is the size of that extent? What would be the size > of the next extent allocated to the index? If an index is created in a > separate dbspace from a table, how can we control the size of extents that > will be allocated to the index? That depends, detached index or attached index? Fragmented table or not? For an attached index on a non-fragmented table: index pages are created in the same extent as data pages using any free pages in any extent assigned to the table. For detached indexes the index is treated like a separate table with no data pages and has its own extents. The size of each extent is a function of the ratio of the rowsize to the key length. For attached indexes on fragmented tables the index is treated as a detached index in the same dbspace as the table fragment it is indexing and is allocated its own entents using the same formula as for detached indexes. Fragmented indexes are just detached indexes spread over several dbspaces. This information is for 7.2+. I think in 7.1x and 7.20 attached indexes on fragmented tables were treated as attached index pages on a non-fragmented table where each fragment could be considered a separate table for this purpose. However, I am not sure about that or where the change occurred. Before 7.xx all indexes were attached and there was no fragmentation. Art S. Kagel