Create/Drop SI

CREATE INDEX SI1(COL1_NM) ON TABLE_NM
DROP INDEX SI_NM ON TABLE_NM

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

Hi Farid,

you'll find all the details in the manuals:

Database Design

Chapter 11: Secondary Indexes

Unique Secondary Indexes

Nonunique Secondary Indexes

Chapter 15: Database-Level Capacity Planning Considerations

Sizing a Unique Secondary Index Subtable

Sizing a Nonunique Secondary Index Subtable

There's no DDL for a index subtable, but it's similar to base tables with following definition:

USI:

create unique index USI(i int) on tab;

create table USI(

internal_overhead byte(7),

i int,

baseROWID byte(8) -- or byte(10) for a partitioned table)

unique primary index(i);

NUSI:

create index NUSI(i int) on tab;

create table NUSI(

internal_overhead byte(7),

i int,

baseROWIDa array of byte(8) -- or byte(10) for a partitioned table)

[non-hashed AMP-local primary] index(i);

That kind of NUSI-PI can't be created on a base table, that's why there's no easy way to calculate the exact size of a NUSI.

Dieter

Tweet