Download (direct link):
Multiple Join Indexes and Memory Considerations
When the Optimizer needs to consider join indexes, its use of memory is controlled by the values in the following fields of the DBSCONTROLGDO:
• MaxParseTreeSegs user-tunable Performance Field
• OptMaxGlobalJI internal field (you cannot view or access this field directly)
The default values for these fields should be sufficient in most cases. However, you should change the default values if the following are true:
• A query returns a 3710 or 3711 error stating that the Optimizer phase has insufficient memory to parse the request
• Your applications use multiple join indexes and/or a hash index and/or a PPI on the same table
In this case, NCR recommends that you run the DBS Control Utility to modify the fields as follows:
IF a target table has .
THEN change this field .
TO this value .
many JIs and/or HIs or a PPI plus an HI and/or one or more JIs
Note: This is an internal field, so does not display in the DBS Control Utility screen.
Contact a Teradata field support representative or the TSC for assistance.
a PPI but no JIs or HIs
"Tools for Troubleshooting Hardware" on page 12-10
"Memory Limitations Related to Partitioned Primary Indexes" in Teradata RDBMS SQL Reference, Volume 4 "DBS Control Utility" in Teradata RDBMS Utilities
"MaxParseTreeSegs" in Teradata RDBMS Performance Optimization
2 - 22 Teradata RDBMS Database Administration
Chapter 2: Building the Teradata RDBMS
Converting Non-Relational Data Structures
Creating and Modifying Indexes
The following statements are available for creating indexes:
IF you want to
Create a PI
CREATE TABLE . [PRIMARY INDEX [PARTITION BY]]
• Only the CREATE TABLE statement can be used to define a new PI.
• Any PI can be single-column or multi-column, partitioned or non-partitioned.
• An NPPI can be unique or non-unique.
• A PPI is allowed to be unique only if all the partitioning columns are included in the set of index columns.
If you do not explicitly specify the column or columns to use as the PI, Teradata RDBMS assigns one by default. The default index type depends on the table definition, as follows:
IF you . THEN Teradata RDBMS assigns .
define a primary key or unique column constraint the first constraint as the default UPI.
do not define a primary key or a unique constraint the first column as the default NUPI.
define the table as a SET (no duplicate rows) table with only one column the single column as the default UPI.
change a default PI to a defined PI
CREATE TABLE, INSERT...SELECT, DROP TABLE, and RENAME TABLE
For instructions, see "Recreating a Table" on page 2-9).
change an existing NPPI
ALTER TABLE .
Note: You cannot use the same ALTER TABLE statement to both alter the basic definitions of a table and modify or revalidate the PI of that table. Use a separate ALTER TABLE statement for each change.
• For an NPPI, you can change or drop the:
- Partitioning; that is, you can change an NPPI to a PPI
• You may define the changed index as UNIQUE if any or all of the following are true:
- The current NPPI is already unique.
- There is a USI on the same set of columns. (If this is the case and the changed index becomes unique, the USI is dropped automatically.)
- The table is empty.
2 - 64
Teradata RDBMS Database AdministrationChapter 2: Building the Teradata RDBMS
Converting Non-Relational Data Structures
IF you want to . THEN use . Comments
change an existing PPI ALTER TABLE [[REVALIDATE PRIMARY INDEX] [ADD/DROP RANGE BETWEEN ... [NO RANGE [OR UNKNOWN]] [DROP RANGE WHERE conditional_expressi on] [MODIFY PRIMARY INDEX]] The capabilities and rules for an NPPI apply. Also, you can change the: • Conditional partition expression (based on the system-derived PARTITION column; see "Rules for Altering the Partitioning Expression for a Primary Index" in Teradata RDBMS SQL Reference, Volume 4.) • Handling of rows outside the range of internal partition numbers • Uniqueness of partitioning columns, adhering to the following rules: - Change a NUPPI to a UPPI only if you include all the partitioning columns in the set of index columns. - Exclude one or more partitioning columns from a UPPI only if you also specify NON UNIQUE (that is, the UPPI must becomes a NUPPI). • Partitioning range (see "RANGE_N" in Teradata RDBMS SQL Reference, Volume 5)
define an SI on a table CREATE INDEX • An SI cannot be partitioned • You can define an SI to be: - Single-column or multi-column - Ordered by value or by hash - Unique or non-unique - If a NUSI, any column can be defined as allowing NULLs • If you want an SI on the same columns as the PI, you can define: - A USI on the PI column set, as long as the PI is a NUPI or a NUPPI. - A value-ordered NUSI on the column set of a NUPI or a NUPPI. - A hash-ordered NUSI on the column set of a NUPPI that does not include all the partitioning columns. Note: CREATE INDEX uses permanent table space as workspace. Verify that PERM space is available for up to twice the expected index size on non-fallback tables, and up to four times the expected index size for fallback tables.