Copy Join and Hash Indexes | Teradata Data Mover - About Copying Join and Hash Indexes - Teradata Data Mover

Copying join and hash indexes between database versions has certain restrictions:

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 example:

<indices>
	<index selection= "included">
		<name>Orders_HI</name>
		<index_database>west1000</index_database>
		<map>sparsemap4amps1</map> 
		<colocate>sparsecol2</colocate>
		<index_type>HASH_INDEX</index_type>
	</index>
</indices>

The parameters and possible values for join and hash indexes are described in the following table:

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.

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 here 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 ;