IDS Forum

Re: Indexes contain multiple extents after reorg

Posted By: Fernando Nunes
Date: Thursday, 8 November 2012, at 7:31 p.m.

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

On Fri, Nov 9, 2012 at 12:08 AM, 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.
>

You can get all that info easily with oncheck -pT db:table

>
> 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.
>
> I would have to check, but the index extent size is based on the table's
EXTENT SIZE and the next index extent size should be based on the table's
next extent size.
What is your table's NEXT SIZE?
You mean you have 72 extents for a bit more than 2000 pages? That is
possibly caused by a very small table's NEXT size.
By the way, 11.70 allows for index extent definition, although most of the
times I find this very unnecessary.

> 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?
>

Overestimate the table's extent (first) size and recreate the index or
change the table's NEXT SIZE.
Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--047d7b67898aeef87904ce0511dd

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.