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

Teradata RDBMS Database Administration - NCR

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

Caution: For every partition processed, one data block is retained in memory. 12K

represents the size of the cylinder index associated with a data block (assuming a separate cylinder index is needed for each data block).

Smaller values can help avoid memory contention. Larger values may improve performance as long as the data blocks can be kept in memory and the number of partitions in a table is not exceeded.

Formulas for usage calculations and instructions on how to access and modify the PPICacheThrP field are given in the "DBS Control Utility" in Teradata RDBMS Utilities.

Revalidating Primary Indexes

The REVALIDATE PRIMARY INDEX option of the ALTER TABLE statement can regenerates table headers and update indexes and internal numbering. You should make it a practice to revalidate primary indexes when:

ARC completes a COPY/RESTORE operation after a migration

ARC completes an ARCHIVE/RESTORE operation for:

A Teradata RDBMS upgrade

A Teradata RDBMS with a different hashing algorithm (if partitioning expressions include the HASHROW or HASHBUCKET function)

You change the RoundHalfwayMagUp field in the DBS Control record (if any partitioning expressions include decimal operations)

Query responses or the CheckTable utility indicate incorrect partitioning

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Submitted without options, REVALIDATE PRIMARY INDEX regenerates only the table headers.

Define the null_partition_handler option if, in addition to regenerating table headers, you also want to:

Update any SIs, JIs, and HIs defined for the table

Update the partition numbers and row hash values

Move rows as necessary, based on the regenerated hash values and partition numbers

Caution: REVALIDATE does not correct rows that are out of order by their assigned row ID; an error occurs. If rows still have incorrect locations or internal partition numbers after revalidation, contact the TSC.

The REVALIDATE process changes the table version number. You cannot execute certain ARC operations over a DDL statement that alters the table structure.

For further details on ARC operations, see:

"Using ARC With Modified PIs or Partitioning" on page 8-8

Appendix B: "Import/Export Utilities"

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

Secondary Indexes (SI)

Secondary indexes are optional and are not related to row distribution. An SI is an index structure in the form of a subtable which is physically separate from the data rows of the base table.

A row of an SI consists of an index value along with one or more identifiers of the base data rows having this index value.

Creating a Secondary Index

You can define none, one, or many SIs on one data table, and any SI can be non-unique (NUSI) or unique (USI). Before you create a USI, consider that:

Teradata RDBMS creates an implicit USI on the set of PI columns when the PI is defined with the PRIMARY KEY or UNIQUE attribute.

A USI is useful on a set of partitioning columns when the PI cannot be unique and/or cannot contain all the partitioning columns.

For the purpose of uniqueness, nulls in a USI are considered equal (which is not true with any other comparison of values).

If a uniqueness violation occurs while attempting to create a USI, the entire transaction is rolled back.

The rows of a USI subtable are hash distributed. This means USI subtable rows might reside on an AMP on which no data rows exist, and data rows might exist on an AMP on which no USI rows exist.

The tools and basic rules for creating a secondary index are as follows:

IF you want to . THEN .
create an SI (unique or not, on a new or existing table) Use the CREATE INDEX statement The index cannot be partitioned
create a USI on the same set of columns as the PI The PI must be non-unique (a NUPI or a NUPPI) You can later alter the table to define the PI as unique (a UPI or UPPI) if one of the following is true: - The PI is a PPI that includes all the partitioning columns - The PI is an NPPI

2 - 22 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

IF you want to . THEN .
alter the PI of a table to be unique (allowed when the table has a USI on the same columns) the USI is dropped automatically. SIs reside in subtables, so dropping the USI: Saves space and overhead Increases, if it is the only SI, the number of client utilities that can operate on the table (see Appendix B: "Import/Export Utilities").
define a value-ordered NUSI allowed on either an NPPI or a PPI as long as: The NUSI is defined on the same set of columns as the PI (partitioned or not) If the PI is a PPI, it does not include all of the partitioning columns

Proving Secondary Indexes
Previous << 1 .. 33 34 35 36 37 38 < 39 > 40 41 42 43 44 45 .. 218 >> Next