Teradata Cookbook

How to do it...

The steps to create a secondary index to improve performance are as follows:

1. Connect to the Teradata database using SQLA or Studio.
2. Check the explain plan in the following query:

/*Explain of Select*/
EXPLAIN SELECT id,dept,emp_Fname,emp_Lname,status from td_cookbook.EMP_SAL
where dept='IT';

1) First, we lock td_cookbook.EMP_SAL for read on a reserved RowHash
to prevent global deadlock.
2) Next, we lock td_cookbook.EMP_SAL for read.
3) We do an all-AMPs RETRIEVE step from td_cookbook.EMP_SAL by way of
an all-rows scan with a condition of ("td_cookbook.EMP_SAL.DEPT =
'IT'") into Spool 1 (group_amps), which is built locally on the
AMPs. The size of Spool 1 is estimated with no confidence to be 2
rows (118 bytes). The estimated ...