Index fragmentation

Rob Fitzpatrick Sponsor
PDSOE is a development tool, not a database utility. You can only use it to inspect database state to the extent that you can achieve within the ABL; reading your application data, the meta-schema and other physical system tables, and the virtual system tables.

That aside, what do you think "index fragmentation" is?

Zach Ahmed

New Member
Hi Rob,
Thank you for getting back to me. I have been MSQL DBA for over 15 years. One of my tasks was to inspect the index fragmentation, look at stats. This to improve the performance of the database. I want to perform the same functions in Progress database. Since I am new to Progress database environment, I like to know how I can archive the same tasks in Progress. From you comments, I assume that my not be possible in PDSOE.

thanks again

Rob Fitzpatrick Sponsor
Zach, some suggestions:
  • download the PDF documentation set for your version of OpenEdge:
  • read the Database Essentials manual
  • read the Database Administration manual
  • in the back of the admin manual there is a reference section; look for "proutil dbanalys qualifier", this will show you how to run a DB analysis report (there are other related qualifiers like tabanalys, idxanalys, and chanalys)
proutil /path/to/dbname -C dbanalys > dbname.dba
This will produce a human readable (and fairly painful to programmatically parse) report of statistics on storage areas, block statistics, table/record/LOB stats, index stats, and overall size stats. Note that content and format may vary with OE version.

As you'll see in the docs, it's also possible in 11.4+ to output to a set of CSV files instead, using the parameters -csoutput, -fieldsep, -csfilePrefix, and -verbose.

If you want throw-away DBs to practice your commands on, each OE installation includes a set of sample DBs in the installation directory (e.g. demo, sports, sports2000, etc.). You can run the prodb command to make a copy of one of these for your use.

Example: prodb mysp sports will create a new DB called "mydb" in the current directory that is a copy of the sample DB called "sports". You can also run prodb without parameters and it will interactively prompt you for the info.

Here is a bit of a sample of what you will see in terms of index stats in a dbanalys report:

Table                      Index  Fields Levels         Blocks    Size  % Util  Factor
  BinNum                      55       1      2              3    6.8K    57.1     1.9
  ItemNum                     56       1      1              1    1.9K    48.5     1.0
  WarehouseNumItemNum         57       2      2              3    6.8K    57.4     1.9

It shows the table name "Bin", prefixed with the schema name "PUB". (An aside: it is possible to have other schemas in a DB that are accessible via the SQL query engine, but all tables that are visible via the ABL are in the "PUB" schema.)

For each of the named indexes of Bin, it shows the following:
  • Index: index number (corresponds to the field _index._idx-num in the meta-schema)
  • Fields: number of index components
  • Levels: number of levels in the index's B-tree, from root to leaf level; this number of blocks would have to be read into the buffer pool for each access to that index
  • Blocks: number of database blocks that make up the B-tree (i.e. the physical size of the index)
  • Size: the logical size of the index keys stored in that index
  • % Util: the ratio of the logical size to the physical size
  • Factor: I'm not sure how this is calculated or whether it is relevant
"Index fragmentation" is not a term that is used with respect to OE DBs. However a low index utilization percentage would indicate an index that could benefit from maintenance of some kind, depending on whether it's of a meaningful size and how much it is accessed by the application(s). That maintenance activity could be compacting the index (look up "proutil idxcompact qualifier") online or offline, or rebuilding the index from the record data, which is an offline activity (look up "proutil idxbuild qualifier"). These utilities also allow you to specify a target utilization factor.

I hope this helps. I'm sure being new to OE you'll have lots more questions; I'll do my best to help.


Zach Ahmed

New Member
Hi Rob,
You are amazing. Than you for all of the information and help you have provided. I will go ahead and look at your suggestions.

thanks again :)