All Forums Tools
Farid Khan 4 posts Joined 11/05
30 Jun 2006
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.ThanksFarid Khan Pathan.

Barry-1604 176 posts Joined 07/05
30 Jun 2006

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.

Luckyhansh 30 posts Joined 08/06
28 Aug 2006

you may see it in dbc.tables

dnoeth 4628 posts Joined 11/04
29 Aug 2006

Hi Farid,you'll find all the details in the manuals:Database DesignChapter 11: Secondary IndexesUnique Secondary IndexesNonunique Secondary IndexesChapter 15: Database-Level Capacity Planning ConsiderationsSizing a Unique Secondary Index SubtableSizing a Nonunique Secondary Index SubtableThere'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

Dieter

John Mac 21 posts Joined 11/05
29 Aug 2006

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

Archana.B.V.N 2 posts Joined 03/09
16 Mar 2009

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

You must sign in to leave a comment.