IDS Forum

Re: TABLE AN INDEX REORG

Posted By: Art Kagel
Date: Friday, 21 August 2009, at 11:30 a.m.

In Response To: TABLE AN INDEX REORG (SUJIT ROY)

If you are deleting and readding similar numbers of rows each month, then
you should not need to reorganize the table periodically. IDS will reuse
the deleted row space efficiently. The indexes are rebalanced and deleted
keys are removed by the BTREE CLEANER threads which run almost constantly in
7.31 (I note your other post). The BTREE SCANNER threads in IDS 10.00 and
later are much more efficient, especially when configured to use ALICE mode,
but the final result is a clean fairly well balanced B+tree index.

BTW there are many reasons for anyone to upgrade from IDS 7.31 to IDS 11.50,
but in your case the improvements in the btree cleaning and other features
is a major win if you upgrade. Note:

- In IDS 11.50xc4 and later the BTREE SCANNER threads can be configured

to more aggressively compress partially empty index nodes making the indexes

more efficient for indexes where the newly added keys are not falling onto

the same nodes as the nodes from which keys were deleted.

- In IDS 11.50xc4 and later there is the new table compression feature

which will significantly save you storage for such large tables and can

improve performance as well

- As part of the compression feature, IDS also added the ability to

reorganize a table in place quickly squeezing out empty space on pages and

shifting all rows to the beginning pages of the table. Optionally you can

release the newly unused space back to the free extent pool for other tables

to use. Note that this makes the new rows more efficient to access as they

will be colocated on pages toward the end of the table.

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Aug 21, 2009 at 7:57 AM, SUJIT ROY <roysujit@hotmail.com> wrote:

> Hi ,
>
> I have a table which stores 40 million record , it is fragmented by month
> clause , it has 12 fragments fro Data and 12 Fragments for Index , the
> table
> keeps a data for a period of 24 months , so each fragment contains two
> months
> data . Each month we delete the oldest months data and new data is inserted
> for particular month.The data spread is more or less uniform over the
> fragments .
>
> I table becomes fragmented , how do I reorg the table and index.The droping
> of
> index and recreating takes lot of time . Is there any good way ,I can do
> this
> fast.
>
> Thanks
> Sujit
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0015174760aa406b250471a88d12

Messages In This Thread

• TABLE AN INDEX REORG
  SUJIT ROY -- Friday, 21 August 2009, at 7:57 a.m.
  • Re: TABLE AN INDEX REORG
    SUJIT ROY -- Friday, 21 August 2009, at 7:58 a.m.
  • Re: TABLE AN INDEX REORG
    Peter_Logan@spartanstores.com -- Friday, 21 August 2009, at 8:10 a.m.
    • Re: TABLE AN INDEX REORG
      SUJIT ROY -- Friday, 21 August 2009, at 8:21 a.m.
  • Re: TABLE AN INDEX REORG
    RALPH GENTRY -- Friday, 21 August 2009, at 8:22 a.m.
  • Re: TABLE AN INDEX REORG
    Art Kagel -- Friday, 21 August 2009, at 11:30 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.