|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/12/2006 10:09:00 AM
Posts: 4,
Visits: 1
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2/18/2008 4:01:01 AM
Posts: 30,
Visits: 14
|
|
|
you may see it in dbc.tables
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/11/2006 6:05:00 AM
Posts: 23,
Visits: 1
|
|
|
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
|
|
|
|