AppLib SHOW IDX CANDIDATES

Syntax

select * from table(applib.show_idx_candidates(schema, table, threshold));
--OR
select * from table(applib.show_idx_candidates(catalog, schema, table, threshold));

Purpose

Use this to give a list of suggested columns for index creation. If the amount of unique values in a column vs. the total number of values is below the passed threshold, and an index does not already exist on that column, the index will be selected for suggestion. (It shows columns with a low unique ratio.)

Parameters

• catalog [VARCHAR(128)]: Optional catalog parameter, defaults to the current session catalog.
• schema [VARCHAR(128)]: Schema containing table being searched.
• table [VARCHAR(128)]: Table whose columns are scanned for index suggestion.
• threshold [INTEGER]: The threshold percent (0 to 100) for accepting a column.

Example

create schema idx_cand_test;
set schema 'idx_cand_test';
create table t1 (
  c1 int,
  c2 int,
  c3 int
);

insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 3), (5, 4, 3);

select * from table(applib.show_idx_candidates('IDX_CAND_TEST', 'T1', 80));

+---------------+----------------+-------------+--------------+
| CATALOG_NAME  |  SCHEMA_NAME   | TABLE_NAME  | COLUMN_NAME  |
+---------------+----------------+-------------+--------------+
| LOCALDB       | IDX_CAND_TEST  | T1          | C2           |
| LOCALDB       | IDX_CAND_TEST  | T1          | C3           |
+---------------+----------------+-------------+--------------+