TABLE AN INDEX REORG
A user on IDS 7.31 asked how to quickly reorganise a 40-million-row table (fragmented by month, 12 data + 12 index fragments, oldest month purged and a new month loaded each cycle), since dropping and recreating indexes was slow. One reply suggested a better fragmentation scheme: empty and drop the old fragment, reassign/create fragments per month (needing spare dbspace room), unloading with HPL if a rebuild is required. Art Kagel argued no periodic reorg is needed when deletes and inserts are balanced, as space is reused and the BTREE CLEANER threads rebalance indexes, and pointed to newer versions' btree scanner/ALICE, compression and in-place table repack features. No single confirmed fix is reported by the original poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
The db version is 7.31
What version of the database .... If 11.50.fc4 you could look at the repack function .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "SUJIT ROY" <roysujit@hotmail.com> To: ids@iiug.org Date: 08/21/2009 07:58 AM Subject: TABLE AN INDEX REORG [16753] Sent by: ids-bounces@iiug.org 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.
Db version is 7.31
Fragmenting a table by month using 12 fragments to hold 24 months of data is not the most efficient method. I have a much larger table with many more fragments. When it comes time to purge data, I delete all rows from the fragment and then drop the fragment. The released fragment is then assigned for the next date range. If possible create new fragments based on month. To use this method, you will need enough free space in each dbspace to hold a copy of the fragment assigned to that space. If any fragment cannot be rewritten in its dbspace, the drop will fail. In that case you will have to unload the entire table and modify the date range for the effected fragments and rebuild the table. To unload many rows, I use HPL.
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