ttRedundantIndexCheck

This procedure scans the indicated table (or all the current user's tables) to find redundant indexes. It returns the names of the redundant indexes and a suggestion for which to drop.

Required Privilege

This procedure requires no privilege.

Related Views

This procedure has this related view.

SYS.V$REDUNDANT_INDEX

Syntax

ttRedundantIndexCheck('tblname')

Parameters

ttRedundantIndexCheck has the parameter:

Parameter Type Description

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 the current user's tables. Using a synonym to specify a table name is not supported.

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 the current user's tables.

Using a synonym to specify a table name is not supported.

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.

Examples

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.