IDS Forum

Re: Indexes contain multiple extents after reorg

Posted By: MARK COLLINS
Date: Thursday, 8 November 2012, at 9:02 p.m.

In Response To: Re: Indexes contain multiple extents after reorg (Art Kagel)

Art,

Thanks for the information. I hadn't calculated the percent of keylength vs. row size. I follow the math that you provided, it makes sense. I just can't see why the index ends up with so many extents based on that math.

It may very well be that the engine assigns 28,263 pages for the first extent, and then adds some additional extents and compresses them due to their contiguous pages, resulting in a first extent that ends up at 35,843. And then, assuming that there were no additional free pages adjacent when it needed to extend the index yet again, it would then base the index's secondary extents on the table's NEXT SIZE.

The part that doesn't make sense to me yet is, why does the index end up requiring almost 38,000 pages when it is less than 10% of the row size? The table is less than 300,000 used pages (312,500 allocated through EXTENT SIZE 625000), so I would expect the index to be around 30,000 pages (I rounded up, I know you came up with 28,263).

I have a spreadsheet where I attempt to calculate such things, and I end up with 1 root page, 8 level-1 non-leaf pages, 520 level-2 non-leaf pages, and 37,370 leaf pages, for a total of 37,899 pages. This compares very favorably to the oncheck -pT output, which shows 36 free pages, 10 bit-map pages (my spreadsheet doesn't attempt to calculate these, but it does include slot table entries and rowids when calculating 72 keys/2k page), and 37,877 index pages, for a total of 37,923. If my spreadsheet can get that close, why doesn't the engine? And why is the total number of pages so much larger than one would expect based on the 9.13% ratio of key length / rowsize? The index actually ended up consuming 12.667% as many pages as the table, and this is immediately after being dropped and rebuilt.

Messages In This Thread

• Indexes contain multiple extents after reorg
  MARK COLLINS -- Thursday, 8 November 2012, at 7:08 p.m.
  • Re: Indexes contain multiple extents after reorg
    Fernando Nunes -- Thursday, 8 November 2012, at 7:31 p.m.
    • Re: Indexes contain multiple extents after reorg
      MARK COLLINS -- Thursday, 8 November 2012, at 8:40 p.m.
  • Re: Indexes contain multiple extents after reorg
    Art Kagel -- Thursday, 8 November 2012, at 8:38 p.m.
    • Re: Indexes contain multiple extents after reorg
      MARK COLLINS -- Thursday, 8 November 2012, at 9:02 p.m.
      • Re: Indexes contain multiple extents after reorg
        Art Kagel -- Thursday, 8 November 2012, at 9:27 p.m.
        • Re: Indexes contain multiple extents after reorg
          MARK COLLINS -- Thursday, 8 November 2012, at 11:46 p.m.
          • Re: Indexes contain multiple extents after reorg
            Fernando Nunes -- Friday, 9 November 2012, at 3:15 a.m.
            • Re: Indexes contain multiple extents after reorg
              MARK COLLINS -- Friday, 9 November 2012, at 10:02 a.m.
              • Re: Indexes contain multiple extents after reorg
                Fernando Nunes -- Friday, 9 November 2012, at 10:54 a.m.
                • Re: Indexes contain multiple extents after reorg
                  MARK COLLINS -- Friday, 9 November 2012, at 11:29 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.