Archives of the TeradataForum

Message Posted: Fri, 18 Nov 2005 @ 11:00:54 GMT

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

Based upon Mr Anonym's inspiration, here's an Erwin Postscript for generation of a Single Table Join Index:

     CREATE JOIN INDEX DEV%TableProp(Database_Nm)_T.%TableName_JI1 AS
     SELECT
      (%TableProp(JI1 COLS1)),(%TableProp(JI1 COLS2))
      FROM DEV%TableProp(Database_Nm)_T.%TableName
     PRIMARY INDEX (%TableProp(JI1 PI COLS));

This expands to this :

     CREATE JOIN INDEX DEVCOE_T.CONENTRY_JI1 AS
     SELECT
      (CON_ID,CON_CREATE_DT),(COE_SRKY_ID,COU_ID_INV,ROWID)
      FROM DEVCOE_T.CONENTRY
     PRIMARY INDEX (CON_ID);

Arguably, the script is somewhat over-engineered as it could just as easily be constructed as:

     CREATE JOIN INDEX DEV%TableProp(Database_Nm)_T.%TableName_JI1 AS
     SELECT
      (CON_ID,CON_CREATE_DT),(COE_SRKY_ID,COU_ID_INV,ROWID)
      FROM DEV%TableProp(Database_Nm)_T.%TableName
     PRIMARY INDEX (CON_ID);

If it's of any use to anyone then so much the better.

_______________________
Dave Clough
Database Designer
Express ICS

www.tnt.com

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