Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 35 36 37 38 39 40 < 41 > 42 43 44 45 46 47 .. 218 >> Next


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 .

References

many JIs and/or HIs or a PPI plus an HI and/or one or more JIs

OptMaxGlobalJI

Note: This is an internal field, so does not display in the DBS Control Utility screen.

8

Contact a Teradata field support representative or the TSC for assistance.

MaxParseTreeSegs

2000

a PPI but no JIs or HIs

MaxParseTreeSegs

2000

"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

THEN use

Comments

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 .

[[REVALIDATE

PRIMARY INDEX]

[MODIFY

PRIMARY

INDEX]]

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:

- Name

- 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 Administration Chapter 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.
Previous << 1 .. 35 36 37 38 39 40 < 41 > 42 43 44 45 46 47 .. 218 >> Next