Show index for a table

 

You can display indexes that are created on a table using iSeries Navigator.

To display indexes for a table, follow these steps:

  1. In the iSeries™ Navigator window, expand the system that you want to use.

  2. Expand Databases and the database that you want to work with.

  3. Expand Schemas and the schema that you want to work with.

  4. Right-click a table and select Show Indexes.

The Show index window includes the following columns:

Table 1. Columns used in Show index window
Column name Description
SQL Name The SQL name for the index
Type The type of index displayed. Possible values are:

  • Keyed Physical File

  • Keyed Logical File

  • Primary Key Constraint

  • Unique Key Constraint

  • Foreign Key Constraint

  • Index
Schema Schema or library containing the index or access path
Owner User ID of the owner of this index or access path
Short Name System table name for the index or access path.
Text The text description of the index or access path
Index partition Partition detail for the index. Possible values:

  • <blank>, For all partitions

  • For Each Partition

  • specific name of the partition
Valid Whether the access path or index is valid. The possible values are Yes or No.
Creation Date The timestamp of when the index was created.
Last Build The last time that the access path or index was rebuilt.
Last Query Use Timestamp when the access path was last used by the optimizer.
Last Query Statistics Use Timestamp when the access path was last used for statistics
Query Use Count Number of times the access path has been used for a query
Query Statistics Use Count Number of times the access path has been used for statistics
Last Used Date Timestamp when the access path or index was last used.
Days Used Count The number of days the index has been used.
Date Reset Days Used Count The year and date when the days-used count was last set to 0.
Number of Key Columns The number of key columns defined for the access path or index.
Key Columns The key columns defined for the access path or index.
Current Key Values The number of current key values.
Current Size The size of the access path or index.
Current Allocated Pages The current number of pages allocated for the access path or index.
Logical Page Size The number of bytes used for the access path or index's logical page size. Indexes with larger logical page sizes are typically more efficient when scanned during query processing. Indexes with smaller logical page sizes are typically more efficient for simple index probes and individual key look ups. If the access path or index is an encoded vector, the value 0 is returned.
Duplicate Key Order How the access path or index handles duplicate key values. Possible values are:

  • Unique - all values are unique.

  • Unique where not null - all values are unique unless null is specified.
Maximum Key Length The maximum key length for the access path or index.
Unique Partial Key Values The number of unique partial keys for the key fields 1 through 4. If the access path is an encoded vector, this number represents the number of full key distinct values.
Overflow Values The number of overflow values for this encoded vector index.
Key Code Size The length of the code assigned to each distinct key value of the encoded vector index.
Sparse Is the index considered sparse. Sparse indexes only contain keys for rows that satisfy the query. Possible values are:

  • Yes

  • No
Derived Key Is the index considered derived. A derived key is a key that is the result of an operation on the base column. Possible values are:

  • Yes

  • No
Partitioned Whether the index partition should be created for each data partition defined for the table using the specified columns. Possible values are:

  • Yes

  • No
Maximum Size The maximum size of the access path or index.
Sort Sequence The alternate character sorting sequence for National Language Support (NLS).
Language Identifier The language code for the object.
Estimated Rebuild Time The estimated time required to rebuild the access path or index.
Held Is a rebuild of an access path or index is held. Possible values are:

  • Yes

  • No
Maintenance For objects with key fields or join logical files, the type of access path maintenance used. The possible values are:

  • Do not wait

  • Delayed

  • Rebuild
Delayed Maintenance Keys The number of delayed maintenance keys for the access path or index.
Recovery Whether the access path is rebuilt immediately when damage to the access path is recognized. The possible values are:

  • After IPL

  • During IPL

  • Next Open
Index Logical Reads The number of access path or index logical read operations since the last IPL.
Index Physical Reads The number of access path or index physical read operations since the last IPL.

 

Parent topic:

Indexes and the optimizer