Secondary index Subtable

Tools & Utilities

Secondary index Subtable

Hi,

Could you please tell me What is the DDL of Secondary index subtable ? I just wanted to know what are the Columns will be there in the secondary index subtable and the Meaning of those columns.

Thanks
Farid Khan Pathan.
5 REPLIES
Enthusiast

Re: Secondary index Subtable

I'm not sure I understand your question.

The secondary index subtable is a "hidden" table automatically maintained by Teradata when you create a secondary index on a table. The DDL to create a secondary index is something like:

create index(col1, col2, etc.) on databasename.tablename;

or you can specify an additional "index" clause when creating the table to build secondary indexes.

You can view the columns contained in secondary indexes by selecting from dbc.indices.

Hope that helps.

Enthusiast

Re: Secondary index Subtable

you may see it in dbc.tables
Junior Contributor

Re: Secondary index Subtable

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
Enthusiast

Re: Secondary index Subtable

If you want to know the Diskspace size of your subtable(s) you can COLLECT DEMOGRAPHICS on the base table into a QCD of your choice

Re: Secondary index Subtable

Hi

Is there any particular reason that subtable for non-unique secondary index is stored on the same amp as that of base table?