Variable Creation - OUTPUT - Primary Index (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software
  1. On the Variable Creation dialog, click on OUTPUT.
  2. Click on primary index.
    Variable Creation > OUTPUT > Primary Index

  3. On this screen, select the columns that 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 the 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).
    • 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) — This field can be used to add 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. As such, it is not validated before execution, so care should be taken to use correct Teradata SQL syntax.

      Note that 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.