You cannot create a partitioned primary index for a hash index, but you can create a hash index on a table that has a row-partitioned primary index in some cases. The rules for creating hash indexes on tables defined with a row-partitioned PI are as follows:
- To define a hash index on a row-partitioned PI 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 row-partitioned PI table if the partitions for the table are defined on PERIOD bound functions.
For example, suppose you create the following row-partitioned PI table with a partitioning expression based on the BEGIN bound function.
CREATE TABLE hash_idx_ppi ( i INTEGER j INTEGER, vt PERIOD(DATE)) PARTITION BY (CASE_N(BEGIN(vt) < 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);
The system returns an error to the requestor indicating that you cannot create an index on a PERIOD column.
- If 3710 errors occur when running queries against a row-partitioned PI 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 row-partitioned PI table.
Problem Occurs with Row-Partitioned PI Tables Suggested Action 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.
For information on changing the setting for the MaxParseTreeSegs DBS Control field, see Teradata Vantage™ - Database Utilities, B035-1102.