Start of Content Area

Procedure documentation Analyzing Indexes by Fill Level, Leaves, and Levels with SAPDBA  Locate the document in its SAP Library structure

Use

You can use SAPDBA for Informix to analyze indexes by fill level, leaves, and index levels.

Prerequisites

Procedure

  1. Choose Reorganization → Analyze Index Reports in SAPDBA for Informix.
  2. Choose one of the following:

    3. - Unique Indexes by Fill Level

    - Indexes by Number of Leaves

    - Indexes by Number of Levels

  3. In the field dbspace, you specify the dbspace that SAPDBA searches for indexes to include in the report.

    4. The default is ALL, that is, indexes from all dbspaces are included in the report. If you do not know the name of the dbspace you want, you can use a partial entry (for example, ps*) and SAPDBA lists the corresponding dbspaces.

  4. In the field threshold value, you specify a minimum value that SAPDBA uses to filter the indexes for the report.

    5. The threshold varies according to the report you have selected, as follows:

    Selection Criterion

    Report title

    Number of index levels

    Unique Indexes by Fill Level

    Number of index leaves

    Indexes by Number of Leaves

    Number of index levels

    Indexes by Number of Levels

    Note

    If possible, set a sensible threshold value to restrict reports to a single dbspace. This means that the average processing time to produce a report is usually between a few seconds and a few minutes. If you set low thresholds and include all dbspaces then the run times are significantly longer.

  5. In the field sort order, you specify how the report is sorted, either ascending or descending.
  6. Execute the report.

    7. SAPDBA displays the report with entries similar to the following example (this report is for number of leaves):

    index

    fill [%]

    levels

    leaves

    table

    1) 1093_2102

    68

    3

    171

    funkt

    2) setdata0

    75

    3

    144

    dokhl

    3) 1439_1502

    56

    3

    138

    dsyad

    4) makt__m

    -

    3

    128

    dsyaw

    5) tbtco__2

    -

    3

    102

    dd031

    .....

    		        

    The indexes are listed in order of the report title. The entries on the report have the following meanings:

    Column

    Meaning

    fill [%]

    Indicates approximately how full the index is. SAPDBA can only derive this figure for unique indexes.

    The report Unique Indexes by Fill Level does not contain any duplicate (that is, non-unique) indexes. With duplicate indexes, SAPDBA would have to scan each table to work out how many unique values there are for the indexed column and this would take too long. Therefore, for the other reports, duplicate indexes are shown with a hyphen in the fill [%] column.

    levels

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

    leaves

    Shows how many leaf pages the index has, indicating how large the index is

    table

    Shows which table the index belongs to

    dbspace

    Shows where the index is stored. If the index is fragmented (possible with Informix version 7.1 or later), it is stored in a number of different dbspaces instead of in a single dbspace.

    This only appears when you specify a dbspace.

    Note

    The figures in this report might be out of date if the statistics on the table owning an index are out of date. In this case, when you select one of the numbers on the left to see detailed index information, SAPDBA offers to update statistics on the table.

  7. To see more detailed storage information for a single index, choose one of the numbers on the left of the report. For more information, see Analyzing Index Information with SAPDBA.
  8. 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 indexes in question. For more information if you want to perform a reorganization, see:

 

See also:

Informix documentation