Start of Content Area

Procedure documentation Analyzing Index Information with SAPDBA  Locate the document in its SAP Library structure

Use

You can use SAPDBA for Informix to comprehensively analyze information about a single index. If you want to look at information on more than one index, see Analyzing Indexes by Fill Level, Leaves, and Levels with SAPDBA.

Prerequisites

Procedure

  1. Choose Reorganization → Analyze Index Reports → Index Information in SAPDBA for Informix.
  2. In index name, enter data as required. If you do not know the name of the index you want, you can use a partial entry (for example, pa*) and SAPDBA lists the matching indexes.
  3. Execute the report.

SAPDBA displays the report.

Note

Sometimes SAPDBA offers to update statistics before the Index Information report screen appears, because the statistics data on the table is out-of-date. You can immediately perform an update for the table concerned to get an accurate report on it. For more information about how to identify tables needing an update and how to perform the update, see Update Statistics with SAPDBA.

The entries on the report have the following meaning:

Entry

Meaning

table name

Shows which table the index relates to

dbspace

Shows where the index is logically stored (an index can be stored in a separate dbspace from its associated table with Informix version 7.1 or later). If fragmented (Informix version 7.1 or later), an index is stored in a number of different dbspaces instead of in one single dbspace.

index type

Shows how the index was created:

  • Unique

Two rows can have the same value for the indexed column(s).
  • Duplicate

Two or more rows can have the same value for the indexed column(s).

fragmentation

Shows whether the index is fragmented or not. With indexes, the fragmentation strategy can be either of the following:

  • table based

The index follows the model of the associated table. This means that the fragmentation strategy of the table must be expression-based rather than round robin since an index cannot be set up as round robin
  • by expression

The index has been set up with its own specific expression, independent of its associated table.
  • detached

The index resides in a separate storage area (that is, in a separate tablespace) from its associated table.

index levels

Shows how many levels the index has, indicating how large the index is

index leaves

Shows how many leaves the index has, indicating how large the index is

index size

Shows approximately how large the index is, in kilobytes. You see one of the following:

  • index size: 126835 KB (+/- 5%)

This means that the figure is given with a confidence level of plus or minus a certain amount. The confidence level in this example is +/- 5% because the table is relatively large: other confidence levels which you might see with smaller tables are +/- 10% or +/- 30%.
  • index size: 2545 KB (TBLspace Disk Util. Rpt for exact value!)

In this case, you also see dbspace: fragmented. For fragmented indexes (Informix version 7.1 or later), the index size is approximate because SAPDBA takes an average sample of the index. If the data is skewed, which occurs sometimes with expression-based fragmentation, then the figure given is less accurate. For more information if you need a very accurate value, see Analyzing a Tablespace with SAPDBA.

The index is not necessarily completely full with data. See index fill level below for how full the index is.

index fill level

Shows approximately how full the index is. Note that duplicate (that is, non-unique) indexes appear with a hyphen in this field, because it would take too long for SAPDBA to derive a fill level.

  1. If the index is fragmented, choose Display Dbspaces Containing Index Fragments to see the dbspaces that contain the index fragments.
  2. For more information about printing the report, see Printing a SAPDBA Report.

Result

You now have a better picture of the storage situation for the index in question. If you want to change the storage parameters on the index, choose Reorganizing a Single Index with SADPBA.

 

See also:

Informix documentation