Re: How is disk space allocated to Indexes?

If you detach the index the extent size of the index is the same size as = the extent size of the underlying table, which is a bit restrictive. In a standard non-detached index the index pages are interleaved with the = data in the same extent. you can see them by doing the following; run oncheck -pt database:tablename. From this output determine the table partnum (Could also have looked in = sysmaster for this info). run oncheck -pp partnum 0 This will provide bitmap output example as follows; example 1: addr stamp nslots flag type frptr frcnt next prev = =20 1082a1 7848484 0 4 FREE 24 2020 0 0 = =20 0:2 2 2 3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 = =20 Example 2: addr stamp nslots flag type frptr frcnt next prev = =20 501732 93023138 0 4 FREE 24 2020 0 0 = =20 0:2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 = =20 ...practically identical rows snipped for the sake of brevity. 384:3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 = =20 416:3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 = =20 448:3 3 3 3 2 2 3 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 = =20 480:3 3 3 3 2 3 3 3 3 3 3 3 3 3 3 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 = =20 In these examples the number to the left of the colon is the character count. The First page to the right of the first colon is a bitmap page. Sometimes this is represented by a 8 rather than a 2. A 3 means a data page that is fully filled. Sometimes this is represented by a 'c'. I am presuming that a 1 is a data page not fully = filled, in case where 8 is bitmap/index, and c is data page filled, = another numeral represents a data page not fully filled. From memory it = is a 4. A 2 is an index or a bitmap page. In the 2nd example the table has been loaded, then had the index created = (The most efficient manner). This places the index at end of the data = pages and will encourage head thrashing. In your case the index rows would be interleaved, since you added the rows = after adding the index. This is one of the very few areas where IMO = Oracle has got a better solution that informix. In oracle you can detach = the index and set an extent size specifically for the index. Don't know = if this is in 7.3 but it would be useful. HTH rgds Peter Tashkoff <tashkop@iname.com> Zespri International Limited Std Disclaimers Apply All rights reserved. No party may use this document to vilify another. Zespri New Zealand Kiwifruit, The World's Finest >>> <apatankar@s1.com> 12/03/98 08:28:46 >>> 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? -----=3D=3D Posted via Deja News, The Leader in Internet Discussion = =3D=3D----- http://www.dejanews.com/ Now offering spam-free web-based newsreading