Re: How is disk space allocated to Indexes?

> 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? It depends. When you say the index is created in the same dbspace, did you use the 'IN dbspace' clause on the CREATE INDEX statement and just name the same dbspace as the table? If so, the index is actually considered a separate tblspace (just like a detached index in a separate dbspace, with the tblspace identifier tracked in sysfragments) and thus has its own extents. If, instead, you did not use the 'IN dbspace' clause, it is my understanding that the index pages are taken from the space allocated to the table. > If a separate extent is allocated > to the index, then what is the size of that extent? The engine determines the index extent size based on the extent size of the table, the key length, fill factor, and (maybe) whether the index is unique or non-unique. This is assuming that the index is built while the table has no data. If the table actually has data, and that data has caused the table to grow to multiple extents, the first index extent is calculated based on the number of actual keys at the time of index creation. For instance, if you have a table such that you can store 50 rows on a data page, and the index allows 200 keys on an index page (these are all numbers grabbed out of the air, by the way), and the table has an extent size of 200 KB (100 pgs), your first index extent would need 28 pages to store all possible keys that could fit in that extent, assuming it is a unique, attached index with a fill factor of 90 (50 rows/page X 100 pages = 5000 keys, divide by 180 keys/index page at fill factor of 90). So the first index extent would be somewhere around the range of 28-32 pages. If, instead, you had the same table, but it now had 200,000 rows, with a first extent of 100 pages, and several additional extents totaling somewhere over 4000 pages, the index would now need 1112 pages, using the same assumptions, to hold the keys already in the table. Because of this, the index initial extent would be somewhere around 1120 pages. The above discussion is based on behavior I have observed in 7.10.UC2 and 7.14.UC1 on HP-UX 9.04 and 10.20. > What would be the size > of the next extent allocated to the index? It's based on the next extent size for the table, key length, fill factor, as above. > 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? You can't. Mark Collins mcollins@us.dhl.com Words that come to mean everything may finally mean nothing; yet their very emptiness may allow them to be filled with a mesmerizing glamour.