Binary Dump & Load a single Area (9.1D, Type 1)

Posted by Grant Holman on 25-Jan-2017 08:01

When performing a binary D & L on just a single area of a DB  whats thebest way to rebuild the indexes?

Possible approaches that I'm aware of:

  • Specify 'all' indexes for proutil idxrebuild.  This seems inefficient as I'd be needlessly rebuilding every index in the DB.
  • Build a list of all the indexes belonging to the tables in the area and run individual proutil idxbuild calls per index.  Seems a bit long winded.
  • Use the 'build indexes' clause during the proutil load stage.  The best option?  Any downside?

Any of the above recommended or something different entirely?

OpenEdge RDBMS - Forum

All Replies

Posted by Rob Fitzpatrick on 25-Jan-2017 08:08

Need to know your platform and OE version.

Don't do idxbuild all or inline "build indexes".

Posted by Rob Fitzpatrick on 25-Jan-2017 08:09

Sorry, missed the version number in the thread title.  

Posted by Rob Fitzpatrick on 25-Jan-2017 08:13

If 9.1D had the interactive idxbuild menu where you can select individual tables and indexes, I would benchmark that against individual idxbuilds.  What's "best" depends somewhat on your data and your system.

Posted by cjbrandt on 25-Jan-2017 08:45

Running a D&L on a single area in 9.1D.  I think I remember that it was faster to use the 'build indexes' option of proutil load for the larger tables and just use idxbuild if the tables were smaller.  It was because idxbuild scans the area with a single thread before building the index, and that time isn't required with the 'build indexes' option of proutil load.

Posted by Grant Holman on 25-Jan-2017 10:04

Thanks for your replies.  I'll do some benchmarking as suggested.

Posted by ChUIMonster on 25-Jan-2017 10:26

It was a long time ago in a galaxy far, far away but, as I recall, the v9 era was when you could occasionally benefit from "build indexes" option.  Maybe not always -- but it was worth testing.

Posted by Grant Holman on 26-Jan-2017 02:00

Thanks Tom.  One last question - if a set of Tables are in Area X and all of their Indexes are in Area Y should I truncate Y before rebuilding indexes?

Posted by George Potemkin on 26-Jan-2017 02:10

> if a set of Tables are in Area X and all of their Indexes are in Area Y should I truncate Y before rebuilding indexes?

Yes, it's a must.

1) It will cut off some phases while idxbuild processing an index area.

2) The location of new index blocks created by idxbuild will be optimal. Otherwise they will /inherit/ the location of old index blocks: old index blocks will be coverted to the chain of free blocks and new index blocks will use free blocks in their order on the chain.

Posted by George Potemkin on 27-Jan-2017 08:16

>  old index blocks will be coverted to the chain of free blocks and new index blocks will use free blocks in their order on the chain.

I seemed to forget that idxbuild rebuilds the free chains. But again truncate of index area will eliminate this operation.

This thread is closed