Batch BTREE Index Modification to Merge via OpenRoad

Hello again!  Today I'm going to share a minor utility I use at least once a month to optimize BTREE indices in our databases.  Quoting from the INGRES help file:

"In order to maintain good concurrency and performance, the btree index is not rebuilt after deletions. Deletions occur at the leaf and data page level, but an empty leaf page is not released. If your environment is one where many deletions are performed, you should occasionally issue the modify to merge statement.
Modify to merge is also important for users with incremental keys, which may incur lopsided indexes after heavy appends to the end of the table.

Not updating the index to reflect unused leaf pages can cause the index to be larger than it might be. For example, if the employee table is keyed on empno, (ranging from 1 to 31) and you fire all employees with empnos less than 16, the btree index would not shrink but would be unbalanced.....

Modify to merge can be more convenient to run than the modify statement. The index is rebuilt, and empty leaf pages are marked as free, but otherwise leaf and data pages remain untouched; therefore this statement is neither as time-consuming nor as disk-space intensive as the modify to btree statement. Modify to merge can be used only with btree tables."

For a database with hundreds of tables it would be time consuming to run the command for each existing BTREE table so I made a simple image which runs the "execute immediate" SQL on each qualified table it finds.  Below are screenshots of the image at work:

Main Window

Click on the ellipsis button to open the Node selection widow.

Node Selection Window

In the frame select the node you want then click on "Select".  If connection to the selected node is successful the previous frame will be populated with a list of existing databases as shown next.

Populated Main Window

Select the database you want to optimize then click on the "Modify to Merge BTree Tables" button.  A new frame will open showing all the available BTREE tables in your selected database as shown below.

To start the process click on the OK button.  The image will then run the modify to merge command on all the tables listed. 

And that's it.  In my personal experience it does speed up the database processes a bit.  You may want to try it too if you are experiencing lags in your frontend apps.  Fair warning though, I suggest running the image when no other user is logged-on.  And one more thing, you must have INGRES privilege to be able to run the actual modify procedure. :)

Here are the links to the actual images files: OR 3.5 OR 4.1

Create a shortcut link to the image using the following target:

OR 3.5: %II_SYSTEM%\ingres\bin\W4GLDEV.EXE runimage <PATH>\u_btree_mod35.img –Tno
OR 4.1 & Up: %II_SYSTEM%\ingres\bin\W4GRUN.EXE <PATH>\u_btree_mod41.img –Tno

<PATH> is where your image is located.  There is no need to specify the node::db pair in the link as the image will let you choose where to connect.

I'm sure there are far better images created for this purpose but for those who find mine useful please do leave a comment. LOL. Cheers!