The RMU Analyze Indexes command provides a maintenance tool for
analyzing index structures and generates a formatted display
of this statistical information. Information is displayed
selectively for storage areas and logical areas, or for a range
of pages in a storage area. You can use the RMU Analyze Indexes
command to analyze the structures of both sorted (including
ranked sorted) and hashed indexes. The following shows sample
output from the RMU Analyze Index command:
$ RMU/ANALYZE/INDEXES MF_PERSONNEL.RDB JH_EMPLOYEE_ID_RANKED
----------------------------------------------------------------------------
Indices for database - RDBVMS_DISK1:[DB]MF_PERSONNEL.RDB;
----------------------------------------------------------------------------
Index JH_EMPLOYEE_ID_RANKED for relation JOB_HISTORY duplicates allowed
Max Level: 3, Nodes: 34, Used/Avail: 8693/13532 (64%), Keys: 133, Records: 0
Duplicate nodes:0, Used/Avail: 0/0 (0%), Keys: 100, Maps: 100, Records:4113
Total Comp/Uncomp IKEY Size: 600/798, Compression Ratio: .75
----------------------------------------------------------------------------
Data included in the statistics display includes the following
information:
o The first line of output identifies the database in which the
analyzed index resides.
o The second line of output:
- Specifies if the index is a hashed index. In the example,
the index is not hashed, so the term hashed does not
appear.
- The index name
- Whether or not duplicates are allowed.
o Third line of output:
- Max Level
The maximum number of levels in the index.
- Nodes
The total number of nodes in the index.
- Used/Avail (%)
The number of bytes used by the index/the number of bytes
available. (The percentage of space used by the index.)
- Keys
The sum of the dbkeys that point directly to data records
plus those that point to duplicate nodes.
- Records
The number of data records to which the Keys (in the
previous list item) point directly.
o The fourth line of output:
- Duplicate nodes
For hashed and nonranked sorted indexes, this is the number
of duplicate nodes in the index. For a ranked sorted index,
this is the number of overflow nodes. With ranked sorted
indexes, Oracle Rdb compresses duplicates using a byte-
aligned bitmap compression. It compresses the list of
dbkeys that point to duplicates and stores that list in
the index key node. Oracle Rdb creates overflow nodes when
the compressed list of duplicates does not fit in one index
key node. This overflow node contains a bitmap compressed
list of dbkeys and pointers to the next overflow node.
Therefore, for ranked sorted indexes, the duplicate nodes
count (overflow nodes) can be zero (0) if the compressed
list of dbkeys that point to duplicates fits into one node.
- Used/Avail (%)
The number of bytes used by duplicate nodes/number of bytes
available in the duplicate nodes. (The percentage of space
used within the duplicate nodes of the index.) This value
can be zero (0) for a ranked sorted index if the number of
duplicate nodes is zero.
- Keys
The total number of dbkeys that point to a duplicate node
or that point to the beginning of a duplicate node chain in
the index.
- Maps (appears only if the index is a ranked sorted index)
The number of duplicate key data record bit maps used by
ranked sorted indexes to represent the duplicate index key
data record dbkeys.
- Records
The total number of data records pointed to by duplicate
nodes. If the index is a ranked sorted index, Records
refers to the number of data records pointed to by
duplicate bit maps.
o The fifth line of output (appears only if the index is
compressed):
- Total Comp/Uncomp IKEY Size
The total byte count of the compressed leaf index keys
(level 1 nodes only)/the total byte count that would be
consumed if the index were not compressed
- Compression ratio.
The calculated ratio of Total Comp/Uncomp. A compression
ratio greater than 1.0 indicates that the compressed index
keys occupy more space than the uncompressed index keys.
For more information on RMU Analyze Indexes and the display
of index keys, refer to the Oracle Rdb7 Guide to Database
Performance and Tuning.