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 .. 29 30 31 32 33 34 < 35 > 36 37 38 39 40 41 .. 218 >> Next

and you do not define either a primary key or unique constraint a NUPI on the first column in the CREATE TABLE statement.
and you define the table as both: Containing only one column A SET table (no duplicates) a UPI on the single column.

2 - 22 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

Letting the PI happen by default probably will not provide the optimum row distribution of a table or the fastest access path to the rows.

Instead, you should consider the following options:

Choose the column or columns with values that best exploit the benefits of the PI, such as even distribution and direct data access.

If a PI based on data values does not give the results you want, consider using an identity column as your PI. The values of an identity column are generated by the system and will be unique if properly defined and used (see "Using an Identity Column as a Primary Index" on page 2-24).

Use the HASHBUCKET, HASHROW, HASHAMP functions to confirm that each PI distributes the rows of its table evenly across the disks (see "Analyzing Row Distribution" on page 2-51.)

More information on PI use and selection is available under:

"Data Distribution Management" in Teradata RDBMS Performance Optimization.

"Selecting a Primary Index" in Teradata RDBMS Database Design Using an Identity Column as a Primary Index

You can use an identity column as a single-column PI if a composite index is not desirable. However, you should do this only if it is the primary path to the table, and you should define it as a UPI to guarantee uniqueness.

An identity column can be used to generate unique primary key values provided that you specify GENERATED ALWAYS ... NO CYCLE.

Values generated for a GENERATED ALWAYS AS IDENTITY column are always unique, but those generated for GENERATED BY DEFAULT AS IDENTITY are only unique if you also specify a UNIQUE constraint on the column.

Note: If you load the same row twice into an identity column SET table, it is not rejected as having duplicate data because it is made unique as soon as an identity column value is generated for it. Therefore, if real-world uniqueness is a concern, some preprocessing must still be performed on rows to be loaded.

For more information on identity columns, see "Using an Identity Column" on page 2-66 and "CREATE TABLE (Column Definition Clause)" in Teradata RDBMS SQL Reference, Volume 4.

2 - 22

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

Benefits of a Using a Partitioned Primary Index (PPI)

Without partitioning, a query that does not specify the whole PI value may require a full file scan, even though it specifies a subset of that value.

Partitioning distributes rows to the AMPs based on the primary index hash value and then, on each AMP, stored according to an expression you define.

Rows with equal values for the expression are stored in the same partition, or subset, on the AMP. When a query specifies a subset of the PI value that can be matched to a partitioning range defined for the table, then only one or a few partitions need to be searched.

The major benefits of a PPI include:

Efficient searches, by eliminating active partitions (and thereby full-table scans) when you define a range constraint on the partitioning columns.

An access path to base-table rows via the PI values.

An alternate access path via the partitioning columns (which eliminates the need for an SI).

Efficient join and aggregation strategies on the PI values.

Partitions are kept open on PPI tables during join processing to minimize the impact of partitioning on join performance. These open partitions may be referred to as contexts in your EXPLAIN text.

Defining a PPI

To define a PPI, use the PRIMARY INDEX . PARTITION BY phrase of the CREATE TABLE statement.

The partitioning expression must be deterministic and based on the columns within a row.

A transaction is rolled back if the evaluation of the expression causes errors (such as divide by zero). If this occurs, you should correct the situation by either:

Removing partitioning from the table

Changing the partitioning expression

Deleting the rows causing the problem

Dropping the table

The following table summarizes the tools, considerations, and resources you should be aware of when you choose to define a PPI.

2 - 22 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

IF you want to . THEN . For more information, see .
create a global or volatile temporary table a PPI is not allowed. "Primary Index" in Teradata RDBMS Database Design Teradata RDBMS SQL Reference, Volume 4,
create a permanent data table with a PPI There is an 8K limit for the system table column that stores the text of a PPI partitioning expression There is a 64K table header limit
Previous << 1 .. 29 30 31 32 33 34 < 35 > 36 37 38 39 40 41 .. 218 >> Next