About the Database Space and Memory Management menu options

You can use the Database Space and Memory Management option to perform the following functions:

To report on database space utilization
To reorganize fragmented database objects
To add free space to the database files

The Database Space and Memory Management menu contains the following options.

Table: Database Space and Memory Management options

Option	Description
Report on Database Space	Select this option to generate a report on space utilization for the currently selected database. The report contains the dbspaces and the physical pathnames of the relational database. For each dbspace, the report displays the name, the amount of free space in KBytes, the file size in KBytes, and the space utilization. The report also displays the transaction log file size and the amount of free space that remains on each of the file systems being used for the database.
Database Reorganize	Select this option to reorganize fragmented database tables and indexes. These actions are performed from the Database Reorganize menu as follows: 1) Defragment All This option automatically determines the database tables and indexes that are fragmented. It then uses the SQL Anywhere REORGANIZE command to defragment the tables and compress the indexes. The equivalent command is nbdb_admin -reorganize. 2) Table Level Defragmentation This option generates a fragmentation report for each database table. For each table, the report includes the TABLE_NAME, number of ROWS, number of ROW_SEGMENTS, and SEGS_PER_ROW. In addition, a * displays in the ! column for an individual table if it would be automatically selected for reorganization by the Defragment All option. A row segment is all or part of one row that is contained on one page. A row may have one or more row segments. The ROW_SEGMENTS value indicates total number of row segments for the table. The SEGS_PER_ROW value shows the average number of segments per row, and indicates whether or not a table is fragmented. A SEGS_PER_ROW value of 1 is ideal, and any value more than 1 indicates a high degree of fragmentation. For example, a value of 1.5 means that half of the rows are partitioned. See About fragmentation. 3) Index Compression This option generates a fragmentation report for each database index and lets the administrator select individual indexes to be compressed. For each index the report includes the TABLE_NAME, INDEX_NAME, TYPE of index, LEVEL, and DENSITY. The index TYPE is one of the following values: PKEY (primary key), FKEY (foreign key), UI (unique index), UC (unique constraint), NUI (non-unique index). In addition, a * displays in the ! column for an individual index if it would be automatically selected for reorganization by the Defragment All option. The LEVEL and DENSITY indicate whether or not an index should be reorganized. The number of levels in the index tree determines the number of I/O operations that are needed to access a row using the index. Indexes with fewer levels are more efficient than indexes with greater numbers of levels. The density is a fraction between 0 and 1 providing an indication of how full each index page is on average. A LEVEL value of 1 is ideal. An index with a LEVEL value of 4 or more or with a level value of 2 or 3 with a density greater than 0.5 is a good candidate for reorganization.
Add Free Space	Select this option to add additional free space to individual dbspaces. Additional free space helps to reduce future fragmentation of the database objects that are stored in the database. When the relational database is initially created or rebuilt, 25MB of free space is automatically added to the data and the index dbspaces. A Rebuild eliminates all free space and then adds back what was initially added when the database was created. If you accidentally add too much free space, a Rebuild can be used to eliminate the space.

Option

Description

Report on Database Space

Select this option to generate a report on space utilization for the currently selected database.

The report contains the dbspaces and the physical pathnames of the relational database.

For each dbspace, the report displays the name, the amount of free space in KBytes, the file size in KBytes, and the space utilization. The report also displays the transaction log file size and the amount of free space that remains on each of the file systems being used for the database.

Database Reorganize

Select this option to reorganize fragmented database tables and indexes.

These actions are performed from the Database Reorganize menu as follows:

1) Defragment All
This option automatically determines the database tables and indexes that are fragmented. It then uses the SQL Anywhere REORGANIZE command to defragment the tables and compress the indexes.
The equivalent command is nbdb_admin -reorganize.
2) Table Level Defragmentation
This option generates a fragmentation report for each database table. For each table, the report includes the TABLE_NAME, number of ROWS, number of ROW_SEGMENTS, and SEGS_PER_ROW.
In addition, a * displays in the ! column for an individual table if it would be automatically selected for reorganization by the Defragment All option.
A row segment is all or part of one row that is contained on one page. A row may have one or more row segments. The ROW_SEGMENTS value indicates total number of row segments for the table. The SEGS_PER_ROW value shows the average number of segments per row, and indicates whether or not a table is fragmented.
A SEGS_PER_ROW value of 1 is ideal, and any value more than 1 indicates a high degree of fragmentation. For example, a value of 1.5 means that half of the rows are partitioned.
See About fragmentation.
3) Index Compression
This option generates a fragmentation report for each database index and lets the administrator select individual indexes to be compressed. For each index the report includes the TABLE_NAME, INDEX_NAME, TYPE of index, LEVEL, and DENSITY. The index TYPE is one of the following values: PKEY (primary key), FKEY (foreign key), UI (unique index), UC (unique constraint), NUI (non-unique index).
In addition, a * displays in the ! column for an individual index if it would be automatically selected for reorganization by the Defragment All option.
The LEVEL and DENSITY indicate whether or not an index should be reorganized. The number of levels in the index tree determines the number of I/O operations that are needed to access a row using the index. Indexes with fewer levels are more efficient than indexes with greater numbers of levels. The density is a fraction between 0 and 1 providing an indication of how full each index page is on average.
A LEVEL value of 1 is ideal. An index with a LEVEL value of 4 or more or with a level value of 2 or 3 with a density greater than 0.5 is a good candidate for reorganization.

Add Free Space

Select this option to add additional free space to individual dbspaces. Additional free space helps to reduce future fragmentation of the database objects that are stored in the database.

When the relational database is initially created or rebuilt, 25MB of free space is automatically added to the data and the index dbspaces.

A Rebuild eliminates all free space and then adds back what was initially added when the database was created. If you accidentally add too much free space, a Rebuild can be used to eliminate the space.

About the Database Space and Memory Management menu options

Feedback

Feedback