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 when they are duplicates of other unique indexes or primary keys.

•Recreate unique indexes as non-unique indexes when they overlap with other unique indexes or primary keys.

•Remove unnecessary unique constraints when they are duplicates of unique indexes, other unique constraints, or primary keys.

•Remove unnecessary indexes when they are duplicates of other explicitly defined indexes and unique indexes, as well as indexes implicitly defined by unique constraints and foreign keys.

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

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 server was last started.

DbAnalyzer detects and reports column default value inconsistencies that may cause problems in the future. For example a SMALLINT sequence used as default value for an INTEGER primary key column stops further inserts after 32767 rows, although the table is far from full according to the primary key. And CURRENT_USER as default for a CHARACTER column can work fine for years, until a person who has a name with Unicode characters starts using the system.

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 -? | --help

 

dbanalyzer -v | --version

Command-line arguments:

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 -g DBNAME

 

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

dbanalyzer -uUSRNAME -pUSRPASS -cSCHNAME -tTBLNAME -a -g 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 MARTHA in database DB:

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

   constraint TT_U unique (c1, c2));

create unique index TT_U_IDX1 on TT (c4, c5);

create unique index TT_U_IDX2 on TT (c5);

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, TT_IDX1 is redundant because the unique constraint TT_U defines the same index implicitly, and the uniqueness of TT_U_IDX1 is redundant due to TT_U_IDX2 .

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 MARTHA -p secretpwd --table=TT --analysis --statistics

 

The output of the example command looks as follows:

-- *** Analyzing table TT in all accessible schemas ***

--

-- Schema: MARTHA

-- Table: TT

--   No primary key

--   Unique indexes:

--     TT_U_IDX1(c4,c5)

--     TT_U_IDX2(c5)

--   Redundant unique indexes:

--     TT_U_IDX1(c4,c5)

--       Duplicate with TT_U_IDX2

--       To only check uniqueness once, use the following SQL:

          DROP INDEX "MARTHA"."TT_U_IDX1";

          CREATE INDEX "MARTHA"."TT_U_IDX1" ON "MARTHA"."TT"("c4","c5");

--   Unique constraints:

--     TT_U(c1,c2)

--   No redundant unique constraints

--   No foreign keys

--   Indexes:

--     TT_IDX1(c1,c2)

--     TT_IDX2(c3)

--   Redundant indexes:

--     TT_IDX1(c1,c2)

--       Duplicate with TT_U

--       To remove it, use the following SQL:

          DROP INDEX "MARTHA"."TT_IDX1";

--   You may change an existing unique constraint or index to primary key with the following SQL:

      ALTER TABLE "MARTHA"."TT" DROP CONSTRAINT "TT_U";

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

--

-- *** Usage statistics report created at 2025-06-18 15:50:56 ***

--

--

-- Schema Name            Object Name           Type            Usecount

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

--

-- MARTHA                 TT                    TABLE                 11

-- MARTHA                 TT_U_IDX1             UNIQUE INDEX           0

-- MARTHA                 TT_U_IDX2             UNIQUE INDEX           0

-- MARTHA                 TT_U                  UNIQUE                 4

-- MARTHA                 TT_IDX1               INDEX                  0

-- MARTHA                 TT_IDX2               INDEX                  2

--

-- 1 table and 5 indexes processed

-- 2 changes suggested

 

In this example, DbAnalyzer lists the implicit and explicit indexes for the table MARTHA.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 an SQL statement to remove this index. Since the unique index TT_U_IDX2 ensures that column c5 will always be unique, it identifies the uniqueness of TT_U_IDX1(c4,c5) as redundant and provides SQL statements to remove and recreate it as a non-unique index.

DbAnalyzer identifies that no primary key is defined for the table. It suggests using the columns of an existing unique constraint as the primary key.

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

Note that all the rows in the report, except the suggested SQL statements, have a leading -- (which in SQL introduces a comment). This is so that the complete report can be used as input to BSQL, to perform all the suggested table and index changes.

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 are kept.