ttRedundantIndexCheck

Description

Scans the indicated table (or all of the current user's tables) to find redundant indexes. Returns the names of the redundant indexes and a suggestion for which to drop.

Required privilege

This procedure requires no privilege.

Syntax

ttRedundantIndexCheck('tblname')

Parameters

ttRedundantIndexCheck has the parameter:

Parameter	Type	Description
`tblName`	TT_CHAR(61)	Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the redundant indexes for all of the current user's tables.

Result Set

ttRedundantIndexCheck returns the result:

Column	Type	Description
`redundancy`	TT_VARCHAR (1024) NOT NULL	The names of redundant indexes and a suggestion for which index to drop.

Example

Create table y with a primary key. Then create index i. TimesTen returns a warning that a redundant index is being created. Create another index, i1. The command fails and TimesTen returns an error. Call this procedure to show the warnings.

CREATE TABLE y (ID tt_integer primary key);
CREATE INDEX i ON y (id);

Warning 2240: New non-unique index I has the same key columns as existing unique index Y; consider dropping index I

CREATE INDEX i1 ON y (id);

2231: New index I1 would be identical to existing index I The command failed.

CALL ttredundantindexcheck ('y');

< Non-unique index SCOTT.Y.I has the same key columns as unique index SCOTT.Y.Y; consider dropping index SCOTT.Y.I >
1 row found.