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> <map>sparsemap4amps1</map> <colocate>sparsecol2</colocate> <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 in the following table.
Parameter | Description |
---|---|
associate_table | Specifies the table that is associated with the join or hash index. For multi-table join indexes, there can be more than one associate_table element. |
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:
|
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:
|
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 ;
- 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.
Join and hash indexes cannot be copied between Teradata and Aster or Hadoop systems.