If ProTop has access to a database analysis, reports will be generated, and a list will be displayed.
These are the reports created in [PROTOPDIR]/rpt. Click the link to see an example:
Report Name | Description |
friendlyName.area.rpt | Existing storage area summary |
friendlyName.dbparam.rpt | Database description, features, and startup parameters |
friendlyName.dict.rpt | Table, index, and field definitions for your application tables |
friendlyName.idxcompact.rpt | A list of indexes that should be compacted, if any (see idxcompact below for more information) |
friendlyName.idxoverlap.rpt | Shows indexes that have common components in the same order with potential candidates for removal (see idxoverlap below) |
friendlyName.metadict.rpt | Table, index, and field definitions for the "_" (metadata) tables |
friendlyName.redundant.rpt | A list of redundant indexes, if any |
friendlyName.rmchain.rpt | Shows RM chain summary statistics for your application tables |
friendlyName.servers.rpt | Database parameters common to all client/server connections |
friendlyName.tbl.rpt | Application table statistics and suggested RPB values |
idxcompact.friendlyName.sh[bat]
Ctrl-r also creates [PROTOPDIR]/bin/idxcompact.friendlyName.sh[bat]. It contains a command line to compact each index that needs to be compacted. This script can be run manually or tweaked as you see fit and run from your scheduler. The script can be run anytime you have resource bandwidth as idxcompact only locks 1-3 index blocks at a time, allowing for other simultaneous read/write activity on the index.
NOTE: See the ProTop File Viewer (v) page for instructions on navigating in the file viewer or hit '?' to bring up a help panel; 'x' or 'q' will exit the report display and return you to the previous screen.
friendlyName.idxoverlap.rpt continued
The idxoverlap report aims to identify indexes with the same components in the same order. If several indexes have the same leading components and you add a new index by copying an existing definition and adding a new component, you might be wasting a lot of storage and suffering a performance penalty by updating indexes you do not need and likely do not even use.
Consider a table with three indexes:
Index 1:
custNum
Index 2:
custNum orderNum
Index 3:
custNum orderNum lineNum
Index 1 overlaps with Index 2 and 3, and 2 overlaps with Index 3.
However, they are not necessarily redundant since (in this case) we likely want to enforce a uniqueness constraint on the fields shown. Knowing about them is helpful, and if you are not enforcing uniqueness constraints with such indexes, you may be able to eliminate some of them.
In the case above, you might argue that you should make orderNum in index 2 a single-component unique index. Order numbers would then be global to the application rather than tied to the customer (which is likely accurate).
You probably wouldn’t do that with line numbers, but you could also sensibly remove custNum from index 3.
This thinking works well with “queries” (FOR EACH and SQL statements) but potentially runs into problems with FIND statements because FIND only uses a single index. Thus, you need to pay attention to using just the relevant fields in a WHERE clause. And, as we all know, expecting programmers to pay attention is asking for trouble. (This particular example might be programmer-resistant, but more complex indexes with more complex WHERE clauses could get much more interesting.)
See also: