The following technique lets Teradata tell you which statistics it could use to improve the query plan for a specific query. To demonstrate, first, we set up some test tables.

CREATE TABLE A(k integer, fk1 integer, fk2 integer)
PRIMARY INDEX(k);
 
CREATE TABLE B(kb1 integer, kb2 integer)
PRIMARY INDEX(kb1);

Now we turn on requesting statistics recommendations like this. The following can be entered as SQL using BTEQ or Teradata SQL Assistant.

diagnostic helpstats on for session;


Then when we ask for an EXPLAIN, we get statistics recommendations after the explain plan text.

EXPLAIN
SELECT * FROM a JOIN b
ON a.fk1 = b.kb1 AND a.fk2 = b.kb2;

Note the recommended COLLECT STATISTICS commands at the end.

Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct TEST_DB."pseudo table" for read on
     a RowHash to prevent global deadlock for TEST_DB.b.
  2) Next, we lock a distinct TEST_DB."pseudo table" for read on a
     RowHash to prevent global deadlock for TEST_DB.a.
  3) We lock TEST_DB.b for read, and we lock TEST_DB.a for
     read.
  4) We do an all-AMPs RETRIEVE step from TEST_DB.a by way of an
     all-rows scan with a condition of ("(NOT (TEST_DB.a.fk2 IS
     NULL )) AND (NOT (TEST_DB.a.fk1 IS NULL ))") into Spool 2
     (all_amps), which is redistributed by hash code to all AMPs.  Then
     we do a SORT to order Spool 2 by row hash.  The size of Spool 2 is
     estimated with no confidence to be 96 rows.  The estimated time
     for this step is 0.00 seconds.
  5) We do an all-AMPs JOIN step from TEST_DB.b by way of a
     RowHash match scan with a condition of ("NOT (TEST_DB.b.kb1
     IS NULL)"), which is joined to Spool 2 (Last Use) by way of a
     RowHash match scan.  TEST_DB.b and Spool 2 are joined using a
     merge join, with a join condition of ("(fk1 = TEST_DB.b.kb1)
     AND (fk2 = TEST_DB.b.kb2)").  The result goes into Spool 1
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with no confidence to be 9,216 rows.  The
     estimated time for this step is 0.03 seconds.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.03 seconds.
     BEGIN RECOMMENDED STATS ->
  7) "COLLECT STATISTICS TEST_DB.b COLUMN KB1".  (HighConf)
  8) "COLLECT STATISTICS TEST_DB.b COLUMN (KB2 ,KB1)".  (HighConf)
  9) "COLLECT STATISTICS TEST_DB.b COLUMN KB2".  (HighConf)
 10) "COLLECT STATISTICS TEST_DB.a COLUMN K".  (HighConf)
 11) "COLLECT STATISTICS TEST_DB.a COLUMN (FK2 ,FK1)".  (HighConf)
 12) "COLLECT STATISTICS TEST_DB.a COLUMN FK2".  (HighConf)
 13) "COLLECT STATISTICS TEST_DB.a COLUMN FK1".  (HighConf)
     <- END RECOMMENDED STATS

You can run the COLLECT STATISTICS commands that appear in double-quotes in order to provide Teradata with the data demographic information that it needs for an efficient plan.

5467:  Cannot drop or MLOAD a table with join or hash indexes.

To find the join indexes that are defined on your table, run the following query:

SELECT  * FROM dbc.indices 
WHERE indextype='J'
     AND databasename='your_database_name'
     AND tablename='your_table_name'
ORDER BY indexname,columnposition;

CREATE TABLE T
(
   UPDATE_DT DATE FORMAT 'YYYY-MM-DD',
   UPDATE_TM INTEGER FORMAT '99:99:99' )
);

To convert the integer time UPDATE_TM to a TIME:

SELECT
  time '00:00:00'
  + cast(update_tm / 10000 AS interval hour)
  + cast((update_tm / 100) mod 100 AS interval minute)
  + cast((update_tm mod 100) AS interval second)
FROM T

To convert the UPDATE_DT, UPDATE_TM to a timestamp(0):

SELECT 
  cast(update_dt AS timestamp(0))
  + cast(update_tm / 10000 AS interval hour)
  + cast((update_tm / 100) mod 100 AS interval minute)
  + cast((update_tm mod 100) AS interval second)
FROM T

CREATE TABLE T( dt date, tm time(0));
 
INSERT INTO T VALUES (date '2009-05-28', time '14:53:45');
 
SELECT dt, tm,
    cast(  dt AS timestamp(0)) +
            (tm - time '00:00:00' hour TO second) ts
 FROM T;

        dt        tm                   ts
----------  --------  -------------------
2009/05/28  14:53:45  2009-05-28 14:53:45

SELECT
  cast( cast( d AS format 'Z(I)' ) AS varchar(30) )
FROM T;

Why do you need the double cast? Well, if you try to convert from a column that has a decimal(n) to a numeric string and you simply cast to a varchar, Teradata will add a decimal point at the end of your string.

CREATE TABLE T(d decimal(18));
INSERT INTO T VALUES(42);
INSERT INTO T VALUES(3);
 
SELECT '['||cast(d AS varchar(20))||']' FROM T;

(('['||d)||']')
----------------------
[3.]
[42.]

SELECT
  '['||cast(cast(d AS format 'Z(I)') AS varchar(20)) ||']'
FROM T;

(('['||d)||']')
----------------------
[3]
[42]