About Copying Join and Hash Indexes - Teradata Data Mover

Copying join and hash indexes between Teradata Database versions has certain restrictions, as noted in the following sections.

To copy a join or hash index from a source database to a target database, specify the selection="included" attribute of the <index> element when defining the join or hash index, as shown in the following code example.

<indices>
		<index selection= "included">
		 <name>Orders_HI</name>
			<index_database>west1000</index_database>
			<index_type>HASH_INDEX</index_type>
			<associate_table> 
				<database>west1000</database>
				<table>orders</table> 
			</associate_table>
	</index> 
</indices>

The parameters and possible values for join and hash indexes are described below.

If you are copying a join or hash index along with the associated tables and the associated tables are being renamed or relocated, the join or hash index is created on the renamed or relocated table on the target. Aliases cannot be used for the associated table names in the join or hash index definition if the associated tables are being renamed or relocated.

The following code example creates a compressed multi-table join index, Cust_Ord_JI.

CREATE JOIN INDEX west1000.Cust_Ord_JI ,NO FALLBACK ,CHECKSUM = DEFAULT AS
SELECT (west1000.C.c_custid ,west1000.C.c_lname ),(west1000.O.o_orderid ,
west1000.O.o_orderstatus ,west1000.O.o_orderdate ) FROM
west1000.Customer C INNER JOIN west1000.Orders O ON west1000.C.c_custid = west1000.O.o_custid )
PRIMARY INDEX ( c_custid );

The following code example creates a non-compressed multi-table join index, Cust_Ord_J2.

CREATE JOIN INDEX west1000.Cust_Ord_JI2 ,NO FALLBACK ,CHECKSUM = DEFAULT AS
SELECT west1000.C.c_custid ,west1000.C.c_lname,west1000.O.o_orderid ,
west1000.O.o_orderstatus ,west1000.O.o_orderdate FROM
west1000.Customer C INNER JOIN west1000.Orders O ON west1000.C.c_custid = west1000.O.o_custid )
PRIMARY INDEX ( c_custid );

The following code example creates an aggregate join index, Monthly_Sales_JI.

CREATE JOIN INDEX west1000.Monthly_Sales_JI ,NO FALLBACK ,CHECKSUM = DEFAULT AS
SELECT COUNT( * ) (FLOAT, NAMED CountStar ),west1000.Daily_Sales.item_id (NAMED Item ),
EXTRACT(YEAR FROM (west1000.Daily_Sales.sales_date ))(NAMED Yr ),
EXTRACT(MONTH FROM (west1000.Daily_Sales.sales_date ))(NAMED Mon ),
SUM(west1000.Daily_Sales.sales )(FLOAT, NAMED Sum_of_Sales )
FROM west1000.Daily_Sales GROUP BY west1000.Daily_Sales.item_id (NAMED Item ),
EXTRACT(YEAR FROM (west1000.Daily_Sales.sales_date ))(NAMED Yr ), 
EXTRACT(MONTH FROM (west1000.Daily_Sales.sales_date ))(NAMED Mon ) 
PRIMARY INDEX ( Item );

The code example below creates a hash index, Orders_HI.

CREATE HASH INDEX west1000.Orders_HI ,NO FALLBACK ,CHECKSUM = DEFAULT
(o_custid ,o_totalprice ,o_orderdate )
ON west1000.Orders ;

Join and hash indexes cannot be copied between Teradata and Aster or Hadoop systems.