Space Analyst for Sybase ASE and IBM DB2 for Linux, Unix, and Windows - Indexes Tab

Go Up to Using Space Analyst

The Indexes tab displays diagnostic information regarding index space inefficiencies that may exist in your database.

The Index Reorganization Candidate Summary section uses a bar chart to count the various types of index space inefficiencies in your database. Note that the reorganization threshold options are used to compute the counts for each type of space inefficiency.

The table below describes the three distinct types of table space inefficiencies that Space Analyst computes:

Problem	Description
BLevel	These indexes have extended beyond the allowable B-level size set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Indexes with extended levels can take longer to scan and can therefore contribute to slower response times.
Next Extent Problems	These indexes cannot obtain their next extent of free space due to a lack of needed contiguous free space in the tablespace. Indexes suffering from this problem may cause space-related errors if more data is added to their parent table. This problem will be highlighted even if the tablespace that contains the table has its autoextend property for one or more datafiles set to true.
Max Extent Problems	These indexes are nearing their maximum extent limit and have exceeded the allowable maximum extent percentage set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Indexes suffering from this problem may cause space-related errors to result if more data is added to their parent table.

The Index Reorganization Candidate Details section provides filters and a grid that you can use to view detailed space diagnostics for all or selected indexes. The grid contains the following data:

Data	Description
Index Owner	The owner of the index.
Index Name	The name of the index.
Index Type	Indicates if the type of index (index, index partition, etc.)
Index (KB)	The total allocated space of the index in KB.
Number of Rows	The number of rows that the parent table contains. Note: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package.
Clustering Factor	Indicates the amount of order of the rows in the table based on the values of the index. If the value is near the number of blocks, then the table is very well ordered. If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
Index Blevel	Indicates the level of the index. Numbers beyond 3-4 can indicate a fragmented index.
Number of Blocks	The total number of blocks allocated to the index. Note: this column may report NULL if the index has not been analyzed via the ANALYZE command or through the DBMS_STATS package.
Max Extent Percent	A percentage indicating how close an object is to reaching its maximum extent limit. High percentages indicate indexes that may be candidates for reorganization.
Number of Extents	The number of extents that the index consumes.
Max Extents	The maximum extent limit for the index.
Can Extent Space	Indicates if the object can allocate its next extent. If 'N', this is because the tablespace does not currently have enough contiguous free space. This problem will be highlighted even if the tablespace has its autoextend property for one or more datafiles set to true.
Next Extent	The next extent (in bytes) that the index will attempt to allocate when more space for the index is required.
Max Free Space	The largest contiguous block of free space that exists in the tablespace where the index resides.
Tablespace Name	The tablespace that contains the index.

Note: The grid can also contain a column for deleted leaf rows if the Get Deleted Leaf Row Statistics option has been selected. Be aware that the calculation of this statistic can take a lot of time for databases with large numbers of indexes. Large numbers of deleted leaf rows can indicate an index in need of reorganization.

IBM DB2 for Linux, Unix, and Windows

The Index Reorganization Candidate Summary section uses a bar chart to represent the various types of index space inefficiencies in your database:

Indexes with Cluster Ratio Problems
Indexes with Used Space% problems
Indexes with Balance Problems
Indexes with Deleted RID problems
Indexes with Empty Leaf Problems
Indexes with Low Uniqueness

Note: Space Analyst uses the options you select in the Reorganization Wizard to compute each type of space inefficiency.

Tip: All of the problems in the Index Reorganization Candidate Summary section except low cardinality can be corrected with index reorganization. The Low cardinality is an indicator of an index where the columns on which it is defined have low level of uniqueness. Such indexes should either be dropped or changed to include columns that will make the index more unique.

The table below describes the options and functionality on the Index Reorganization Candidate Details section:

Option	Description
Tablespace	Displays all Tablespaces (regular/large) in the database and the value [All].
Index Schema	Displays all schema in the database and the value [All].
Display	All Indexes Indexes with Cluster Ratio Problems Indexes with Used Space% problems Indexes with Balance Problems Indexes with Deleted RID problems Indexes with Empty Leaf Problems Indexes with Low Uniqueness
Refresh	Refreshes the index grid with criteria selected in the above lists.

The Index Reorganization Candidate Details section provides filters and a grid that you can use to view detailed space diagnostics for all or selected indexes. The grid contains the following data:

Data	Description
Index Schema	The schema name for the index.
Index Name	The name of the index.
Table Schema	The schema name for the base table
Table Name	The base table name.
Table Cardinality	The number of rows in the base table.
Avg Key Size	The index key size, calculated from the average column length of all columns participating in the index.
Leafs	The total number of index leaf pages.
Pseudo Empty Leafs	The number of pseudo empty index leaf pages. A pseudo empty index leaf page is a page on which all the RIDs are marked as deleted, but have not been physically removed.
Pseudo Deleted RIDs	A RID that is marked deleted. This statistic reports pseudo deletes RIDs on leaf pages that are not pseudo empty. It does not include RIDs marked as deleted on leaf pages where all the RIDs are marked deleted.
Levels	The number of index levels.
Unique Entries	The number of unique index entries that are not marked deleted.
Page Size	The page size of the table space in which the table indexes reside, specified at the time of table creation.
% Free	Specifies the percentage of each index page to leave as free space, a value that is assigned when defining the index.
Cluster Ratio	The clustering ratio of an index should be greater than 80 percent. When multiple indexes are defined on one table, some of these indexes have a low cluster ratio. (The index sequence is not the same as the table sequence.) This cannot be avoided. Be sure to specify the most important index when reorganizing the table. The cluster ratio is usually not optimal for indexes that contain many duplicate keys and many entries.
Used Space%	Less than 50 percent of the space reserved for index entries should be empty.
Balance	To determine if recreating the index would result in a tree having fewer levels. This formula checks the ratio between the amount of space in an index tree that has one less level than the current tree, and the amount of space needed. If a tree with one less level could be created and still leave PCTFREE available, then a reorganization is recommended. The actual number of index entries should be more than 90% (or 100-PCTFREE) of the number of entries an NLEVELS-1 index tree can handle (only checked if NLEVELS>1).
Deleted RIDs	The number of pseudo-deleted RIDs on non-pseudo-empty pages should be less than 20 percent.
Empty Leaf	The number of pseudo-empty leaf pages should be less than 20 percent of the total number of leaf pages.
Uniqueness	100 * Index Cardinality / Table Cardinality - A low index uniqueness may indicate the need to Alter or Drop the index.
Reorg	An indicator of how severe the fragmentation problem for the index is. Each hyphen (-) displayed in this column indicates that the calculated results were within the set bounds of the corresponding formula, and each asterisk () indicates that the calculated result exceeded the set bounds of its corresponding formula: - or on the left column corresponds to Cluster Ratio - or * in the second from left column corresponds to Used Space% - or * in the middle column corresponds to Balance - or * in the second column from the right corresponds to Deleted RIDs - or * on the right column corresponds to Empty Leaf
Tablespace	The tablespace where the table data resides.
Indexspace	The tablespace where the index data resides.

Available Functionality

The following functionality is available in the Command menu, shortcut menu, and toolbar:

Note: Functionality differ by platform.

Space Analyst for Sybase ASE and IBM DB2 for Linux, Unix, and Windows - Indexes Tab

Topics

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation

Tools