Kbase 16567: How to Index Rebuild with sorting on a large database?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/2/2007 |
|
Status: Verified
GOAL:
How to Index Rebuild with sorting on a large database?
GOAL:
Speed Sorting Indexes
GOAL:
How to sort only some indexes?
GOAL:
How to get the tables and indexes for the index.file?
FACT(s) (Environment):
All Supported Operating Systems
Progress 6.x
Progress 7.x
Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
When your database gets to be larger then 2.6 gigabytes, a full index rebuild with disk sorting might cause the sort (.srt) file to grow past the 2 Gb file size limit. It is estimated you will need about 75% of your database size during index rebuild for your sort file, or even more if the database contains may word indexes.
In version 8.0B and above you can overcome this limit by using multi-volume sort files during the index rebuild process. Please see Progress Solution P6426 for further details on multi-volume sort files.
On the earlier versions of the product, or if you do not have enough disk space to hold such big sort files, the best course of action is to rebuild some of the indexes and do multiple idxbuild until all indexes are rebuilt.
In case of disk space restrictions, you may also want to read Progress Solution 13268 to estimate how big your sort file will grow.
SPEED SORTING
Use the following parameters to speed the index rebuild process:
UNIX: proutil <dbname> -C idxbuild -TB 24 -TM 32 -B 512
VMS: PROGRESS/UTILITIES=IDXBUILD dbname /TBLOCKS=24/MERGE_NUM=32 /BUFFERS=512 You can increase the -B associated with this command to allow more room for managing the indexes in memory. If you receive an error number 1450 when doing an idxbuild with a larger -B, then you will need to decrease the -B appropriately.
HOW TO SORT ONLY SOME OF THE INDEXES
Using the above command and parameters for index rebuild we now need to define the indexes you want rebuilt. If you do this interactively, you can manually enter every table and index you wish to rebuild. Or you could use a file with table and index names in it and redirect the file to the idxbuild command. example ->
proutil <dbname> -C idxbuild -TB 24 -TM 32 -B 512 < index.file
(where index.file is the file with your table and index names) HOW TO GET THE TABLES AND INDEXES FOR THE index.file
This small piece of code will generate a file in the correct format to redirect into idxbuild command (some modifications are needed) ->
OUTPUT TO index.file.
FOR EACH _file WHERE _file-number > 0:
FOR EACH _index OF _file:
DISPLAY _file-name SKIP _index-name SKIP WITH NO-LABELS.
END.
END.
This is a sample of the output from the demo database ->
agedar
ar_cust
agedar
ar_inv
agedar
ar_invdat
customer
cust-num
customer
name
customer
zip
(notice the table,index,table,index,table,index,... pattern) Finally, you will need to split this file to ensure the sort file will not exceed 2 gigabytes. Then you will need to add some commands to the file. You will need to add "some" to the first line of the file and "!" "Y" "Y" as the last three lines of the file. So taking the above file and just rebuilding the 'agedar' table, the file would look like this ->
some
agedar
ar_cust
agedar
ar_inv
agedar
ar_invdat
!
Y
Y
So the above idxbuild command, speed sorting parameters, and index.file will rebuild the 'agedar' table indexes the fastest way possible.