Build ADS - OUTPUT - Primary Index (Teradata Database) - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Teradata Warehouse Miner
Release Number
November 2018
English (United States)
Last Update
Product Category

Before running the analysis, define Output options.

  1. On the Build ADS dialog, click on OUTPUT.
  2. Click on primary index.
    Build ADS > Output > Primary Index

  3. On this screen, select the columns which comprise the primary index of the output table.
    • Select Primary Index Columns for the Output Table — This is the normal option for defining primary index information for the output table, using the rest of the options on the screen.
    • Create Table with NO PRIMARY INDEX — Creates an output table with a NO PRIMARY INDEX clause, and makes the other options below it on the screen invisible, since they do not apply when this option is selected.

      Creating a table with NO PRIMARY INDEX is an option introduced in Teradata 13.00, and as such, this and the previous option are not displayed when an older version of Teradata is in use.

    • Available Columns — A list of columns that will be in the output table or result set. Select columns by highlighting and then clicking on the arrow button to move highlighted columns into the Primary Index Columns window.
    • Primary Index Columns — A list of columns that will comprise the primary index of the output table if one is created (that is, depending on the options selected on the Output - storage tab). These will default to the primary index columns of the anchor table, but only if they are also selected as input columns.
    • Create the index using the UNIQUE keyword — Select whether or not the primary index should be a unique primary index (i.e., that more than one row may not have the same combination of primary index column values).
    • Additional Information (such as Partitioning) — Adds partitioning information for the primary index and/or to add one or more secondary index definitions. The information entered is free-form SQL text that will be placed immediately after the primary index definition when the Output - storage option to Store the tabular output of this analysis in the database (i.e., create an output table) is used.
      The information entered in this field is not validated before execution, make sure to use correct Teradata SQL syntax.

      This field may be entered without error even if the option to create an output table is not used. In this case it will be ignored unless the output of this analysis is referenced as input to another analysis, in which case the additional index information is used on the volatile table that is created.

      In the following example, the additional information both partitions the primary index and adds three secondary indices named idx1, idx2 and idx3.

      PARTITION BY (RANGE_N (cust_id BETWEEN 1362480 AND 1363495 EACH 100))
      INDEX idx1 (age) UNIQUE INDEX idx2 (cust_id, age) INDEX idx3 (income)

      Finally, the Additional Information field may be used even if the Output - post processing screen is used. The difference is that the “additional information” becomes part of the Create Table … As statement, whereas post processing information is used to create separate statements issued after Create Table … As.

    For general information about output, see OUTPUT Tab in the Teradata Warehouse Miner User Guide (Volume 1), B035-2300.