Archives of the TeradataForum

Message Posted: Mon, 26 Nov 2007 @ 14:25:04 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Thanks to Chris and Dieter.

So types "1" and "2" are coming back form the dead!

Original question:

I seem to recall that Join Indices were defined with IndexTypes of 1 or 2 (back in the day).

Is my recall correct? If yes what did/do "1" and "2" mean.

The findings are summarized below:

Per Chris Stephens:

Columns in compressed join indexes may appear as types 1 or 2 in DBC.Indexes (or the DBC.Indices view).

Columns with IndexType = 1 are columns in the "field 1" compressed portion of the join index, and columns

with IndexType = 2 are in the "field 2" uncompressed portion of the index.

Per Dieter Noeth:

Both are used for compressed JIs.

Those values vanished from the Dictionary Manual, but in TD12 it's back again:

1 (field1 column of a join or hash index)

2 (field2 column of a join or hash index)

From the TD Data Dictionary Manual:

Returns the type of an index as:

* P (Nonpartitioned Primary)

* Q (Partitioned Primary)

* S (Secondary)

* J (join index)

* N (hash index)

* K (primary key)

* U (unique constraint)

* V (value ordered secondary)

* H (hash ordered ALL covering secondary)

* O (valued ordered ALL covering secondary)

* I (ordering column of a composite secondary index)

* M (Multi-Column Statistics)

Rgrds,

Bill Gregg
Teradata Team
Thomson Healthcare

	<Prev	Next>		<<First	<Prev	Next>	Last>>