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

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

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.