IDS Forum

Re: Indexes contain multiple extents after reorg

Posted By: Art Kagel
Date: Thursday, 8 November 2012, at 8:38 p.m.

In Response To: Indexes contain multiple extents after reorg (MARK COLLINS)

Mark,

I think that Fernando is correct. You modified the table's first extent
size and not its NEXT EXTENT size. However, the index's first and next
extent sizes are calculated from the table's sizes by applying the ratio of
the key length to the table's rowsize. Your key is about 9% of the
rowsize, so the index's calculated first extent size would be (625000 *
0.09) = 56,525K or 28,263 pages. It looks like the average next extent
added to the index was between 28.888K so it looks like the table's NEXT
SIZE may set to about 325 but maybe much less if many of the table's
smallest extents are much smaller than 29K it is likely that the larger
ones (as well as the additional 7000K in the first extent beyond the
calculated size) are the result of extent compression (compressing multiple
contiguous extents into a single larger one) and the table's NEXT SIZE is
actually much smaller than that!

When it allocates extents, the engine will allocate new extents first from
the smallest block of free space equal to or larger than the requested
extent. That is to prevent fragmenting larger blocks of free space and to
save them for larger extent requests. This will result in a badly
fragmented table or index if the next size is set too small.

So, increase the table's NEXT SIZE to something like 10% of its EXTENT
SIZE, also if you want a single extent for an index that has a key 9.1% of
the rowsize and which needs 38000 pages you will need to set EXTENT SIZE to
832200.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Thu, Nov 8, 2012 at 7:08 PM, MARK COLLINS <markc@myfastmail.com> wrote:

> Informix 11.50.FC6WE, HP-UX 11.31 PA-RISC.
>
> I have a process in place that periodically drops and re-creates indexes to
> consolidate multiple extents into a single extent. For most cases, this
> works
> fine, but there are several indexes which end up with exactly the same
> number
> of extents after the rebuild process as they had before.
>
> For example, I have a table with 2,690,628 rows, rowsize 219. Statistics
> have
> been updated, so the row count is correct. The table has npused = 299034 in
> systables. There are no variable length columns in this table. There is an
> index on a date and fixed char column, for a key length of 20. The index
> had
> 73 extents, the first of which was the largest, at 35843 - I assume this is
> pages (since this is an odd number ), it came from sysptnext.pe_size.
>
> Since the CREATE INDEX statement does not have a provision for specifying
> first/next sizes, I assume that the engine assigns these sizes based on
> information about the table. Based on testing, it seems that the index
> extent
> sizes are based on the table's first extent size, although it seems that
> next
> extent size can have some influence in assigning the size of subsequent
> index
> extents.
>
> I tried to influence the first extent size by doing an 'ALTER TABLE ...
> MODIFY
> EXTENT SIZE 625000;'. I chose that value by multiplying npused * 2 and
> rounding up a bit. The index still had 73 extents, with the first/largest
> still 35843. Adding up the pe_size for all of the extents gives a total of
> 37923.
>
> There are two chunks in this dbspace. One is completely free, other than
> the 3
> reserved pages, with 2499997 free pages. The other has 2544688 free pages,
> with 303,600 free pages at the end of the chunk. Thus, either chunk should
> have ample space to accomodate this index as a single extent.
>
> What can I do to get the index to rebuild in a single extent?
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--14dae9399c7132ab3d04ce0603bb

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.