15.00 - Creating Hash Indexes for Tables With Partitioned Primary Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Creating Hash Indexes for Tables With Partitioned Primary Indexes

You cannot create a partitioned primary index for a hash index, but you can create a hash index on a table that has a partitioned primary index in some cases. The rules for creating hash indexes on tables defined with a PPI are as follows.

  • To define a hash index on a PPI table, you must also specify an ORDER BY clause that includes an explicit column list.
  • The column list cannot contain BLOB, CLOB, Period, or Geospatial columns.
  • You cannot define a hash index on a PPI table if the partitions for the table are defined on PERIOD bound functions.
  • For example, suppose you create the following PPI table with a partitioning expression based on the BEGIN bound function.

         CREATE TABLE hash_idx_ppi (
           i  INTEGER
           j  INTEGER,
           vt PERIOD(DATE))
                       NO CASE));

    Suppose you then attempt to create a hash index on hash_idx_ppi such as the following.

         CREATE HASH INDEX hx(i)
         ON hash_idx_ppi
         ORDER BY VALUES(i);

    Teradata Database aborts this request and returns an error to the requestor indicating that you cannot create an index on a PERIOD column.

  • If you experience 3710 errors when running queries against a PPI table with a hash index, you can increase the amount of memory that is available to the Optimizer by updating the MaxParseTreeSegs DBS Control flags to a value that ensure that it can process any request on the PPI table.

    IF the problem occurs with PPI tables …

    THEN …

    without hash or join indexes

    change the value for MaxParseTreeSegs to this value.

  • 2000 (byte‑packed format systems)
  • 4000 (byte‑aligned format systems)
  • with hash or join indexes

    contact Teradata support.

    You might find that your query workloads are such that these values need to be increased still further.

    See Utilities: Volume 1 (A-K) for information about how to change the setting for the MaxParseTreeSegs field in the DBS Control record.