Archives of the TeradataForum

Message Posted: Wed, 29 Mar 2006 @ 20:44:35 GMT

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

Hi,

I happend to use Winddi and did this operation to find the indexes defined on a table, I right clicked on a table and clicked on indexes, it gave me the list of indexes defined on that table. Then I opened the SQL history to see the SQL generated and found the following sql

     SELECT IndexName,ColumnName,
     CASE IndexType
     WHEN 'P' THEN 'Primary'
     WHEN 'S' THEN 'Secondary'
     WHEN 'K' THEN 'Primary Key'
     WHEN 'U' THEN 'Unique Constraint'
     WHEN 'Q' THEN 'Partitioned'
     WHEN 'V' THEN 'Value Ordered'
     WHEN 'J' THEN 'Join Index'
     WHEN 'N' THEN 'Hash Index'
     WHEN 'O' THEN 'Value Order (All)'
     WHEN 'H' THEN 'Secondary (All)'
     ELSE
     IndexType END (TITLE 'Type'),
     UniqueFlag AS "Unique",
     IndexNumber,
     ColumnPosition
     FROM dbc.Indices WHERE DataBaseName=? AND TableName=? AND IndexType NOT
     IN ('1','2','M') ORDER BY 5,6;

Can someone help me why the query has IndexType NOT IN ('1','2','M') and what index types do they indicate ?

Also what are these Values Order (All) and Secondary ( All) indexes? What is the use of storing Column Position in indices table and what is the benefit of giving indexes a name, we never give index a name and we dont see any problem with that.

TIA

NeevaN.

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