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.
|
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.
- Analysis Report
- Analyze
- DBCC
- Drop
- Extract
- Estimate Size
- Map
- Indexes Wizard (DB2 LUW)
- Open Wizard
- Place
- Quick Reorganize /Reorg
- Reorganize
- Threshold Options
- Update Statistics