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

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.05
Published
October 2020
Language
English (United States)
Last Update
2020-11-18
dita:mapPath
xxl1591023586203.ditamap
dita:ditavalPath
mpm1591127278842.ditaval
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem

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:

Parameter Description
colocate Co-location name, for sparse map only.
index_database Database name in which the join or hash index resides.
index_type Type of index.
Valid values are:
  • HASH_INDEX
  • JOIN_INDEX
map Object map name.
name Join or hash index name.
selection Element that indicates whether the join or hash index is copied.
Valid values are:
  • included to copy the join or hash index
  • unselected to exclude the join or hash index from being copied
The MAPS feature parameters, map and colocate, are supported only in target systems using Teradata Database 16.10 or later.

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 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 ;
When copying join and hash indexes, take the following into consideration:
  • If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.
  • Copying a table that exists on the target with an associated join or hash index overwrites the target table and recreates the join or hash index on the newly copied table.