DbAnalyzer - index analysis

Mimer dbanalyzer is a tool to provide optimization recommendations for a database schema, based on the analysis of primary keys, unique constraints and indexes. The current version provides the following optimization recommendations:

•Remove unnecessary unique indexes that are duplicate with other unique indexes or primary keys.

•Remove unnecessary unique constraints that are duplicate with any unique indexes, other unique constraints or primary keys.

•Remove unnecessary indexes that are duplicate with other explicitly defined indexes and unique indexes, as well as indexes implicitly defined by unique constraints and foreign keys.

•Suggest primary keys for tables without explicitly defined primary keys, based on the defined unique constraints and unique indexes.

The tool is also used to report the usage statistics of indexes and tables. For each index and table, the tool summarizes how many SQL and stored procedure statements have used the index and accessed the table, since the latest start of the server.

Command syntax

dbanalyzer is controlled by the following command-line parameters:

dbanalyzer [-m|-s] [-u user] [-p password] [-e program] [-i programpass]

             [-c schema] [-t table] [-a] [-g] [database]

 

dbanalyzer [-v version]

Command-line Arguments (Unix):

Command examples

The following command analyzes all tables and their indexes in database DBNAME that user USRNAME with a password USRPASS has access to:

dbanalyzer --username=USRNAME --password=USRPASS DBNAME

 

The following command reports usage statistics of all tables and their indexes in database DBNAME that user USRNAME with a password USRPASS has access to:

dbanalyzer --username=USRNAME --password=USRPASS --statistics DBNAME

 

To include both schema analysis and usage statistics of the above tables and indexes:

dbanalyzer -uUSRNAME -pUSRPASS --analysis --statistics DBNAME

 

For all tables in a particular schema SCHNAME in the previous example:

dbanalyzer -uUSRNAME -pUSRPASS -cSCHNAME -a -q DBNAME

 

For a particular table TBLNAME in schema SCHNAME, in database DBNAME, that user USRNAME has access to:

dbanalyzer -uUSRNAME -pUSRPASS -cSCHNAME -tTBLNAME -a -q DBNAME

 

One may also analyze the tables and indexes that a program ident PROGNAME with password PROGPASS has access to:

dbanalyzer -uUSRNAME -pUSRPASS -ePROGNAME -iPROGPASS -cSCHNAME -tTBLNAME -a -g DBNAME

 

VMS:On VMS, arguments are converted to lower case by default. To retain upper case characters, enclose the argument in double quotes, e.g. -p"SecretPwd".

An example with output

Consider the following table created by user SYSADM in database DB:

create table TT (c1 int not null, c2 int not null, c3 int,

    constraint TT_U unique (c1, c2));

create index TT_IDX1 on TT (c1, c2);

create index TT_IDX2 on TT (c3);

 

Note that table TT doesn't have a primary key, and TT_IDX1 is redundant because the unique constraint TT_U defines the same index implicitly.

Assume that 11 statements have accessed table TT, 2 of them have used index TT_IDX2, and 4 have used the implicit index created by TT_U.

Each statement may have been executed many times since the server started.

The following command is executed to analyze the table TT and report the usage of the table and its indexes:

dbanalyzer DB -u SYSADM -p SYSADM -s SYSADM --table=TT --analysis

 

The output of the example command looks as follows:

****Index and unique constraints optimization suggestions*****

Start analyzing table SYSADM.TT

 Used by 11 statements since server started

 Unique constraints:

   Unique constraint TT_U(c1, c2)

       Implicit index used by 4 statements since server started

 No redundant unique constraints are found

 No foreign keys are found

  Indexes:

   Index TT_IDX2(c3)

       Index used by 2 statements since server started

   Redundant index TT_IDX1(c1, c2)

       Index used by 0 statements since server started

       Duplicate with TT_U. To remove it, use the following SQL:

       DROP INDEX TT_IDX1 RESTRICT;

  Primary key is not defined. You may change an existing unique constraint or index to primary key with the following SQL:

   ALTER TABLE TT DROP CONSTRAINT TT_U;

   ALTER TABLE TT ADD CONSTRAINT PK_TT PRIMARY KEY (c1,c2);

 

==========================================================================

Usage statistics report created at 2022-05-12 15:44:01

Indexes are printed following their respective tables

--------------------------------------------------------------------------

Schema Name             Object Name             Type        Use Count

 

SYSADM                  TT                      Table       11

SYSADM                  TT_U                    Index       4

SYSADM                  TT_IDX1                 Index       0

SYSADM                  TT_IDX2                 Index       2

==========================================================================

 

In this example, dbanalyzer lists the implicit and explicit indexes for the table SYSADM.TT, and checks if any of them are redundant. It identifies TT_IDX1 as a duplicate index of the unique constraint TT_U, and provides SQL statements to remove this index.

dbanalyzer identifies that no primary key is defined for the table. It suggests to use the columns of an existing unique constraints as the primary key.

The tool reports how many statements have used each table and index. These numbers are also summarized in a table in the end of the report.

Notes

A user or program ident can only see analysis of tables and indexes which it has SELECT privilege to. However, the summarized usage statistics of each table/index contain the statements used by all users and programs.

Only statistics for statements retained by the server is kept.