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 forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 128 129 130 131 132 133 < 134 > 135 136 137 138 139 140 .. 241 >> Next


If the col_name is present then it must be the referenced table single column PRIMARY KEY, or a referenced table single column defined with the UNIQUE attribute.

As noted in the CREATE TABLE syntax, UNIQUE and PRIMARY KEY may be defined as constraints on a table. The internal Teradata implementation of these constraints is as indexes, with the restrictions that these indexes are always UNIQUE, and cannot have nullable columns.

The Teradata RDBMS also allows the syntax [ UNIQUE [ PRIMARY] INDEX. Non-unique primary and secondary indices (NUPIs and NUSIs) are still supported.

Non-unique indexes are flagged as non-ANSI when the SQL flagger is enabled.

Selection of indexes is an important aspect of database design.

The primary index uses a hashing algorithm to determine where rows are stored on the AMPs of the Teradata RDBMS. Selection or update operations that use the primary index in a WHERE clause may use this hashing algorithm to determine the location of any row with maximum efficiency.

Index Types

Primary Indexes

UNIQUE PRIMARY KEY Versus INDEX

8-84

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Secondary Indexes

PRIMARY KEY and UNIQUE Constraints Vs. PRIMARY INDEXES

You cannot compress columns that are components of a primary index.

Up to 32 secondary indexes can be defined for one table, and any secondary index can be unique or non-unique.

Secondary indexes also may be added to an existing table with the CREATE INDEX statement. Each secondary index requires the creation of a subtable; insert, delete, and update operations require more processing time when secondary index subtables also need to be changed.

Secondary indexes can greatly reduce table search time. Each row in a secondary index subtable contains the index value and one or more row IDs that point to the data row(s) containing that value.

The subtable of a unique secondary index is a hashed table and is efficient for selecting a single row or a small number of rows. Bit mapping of the row IDs of non-unique secondary indexes provides efficient retrieval when complex conditional expressions are applied to very large tables. It is advisable to test the performance of a secondary index before implementing it.

The user is allowed to define the primary indexes using PRIMARY KEY and UNIQUE constraints syntax in a CREATE TABLE statement.

The rules are:

A table can have at most only one PRIMARY KEY and must have one PRIMARY INDEX.

If both a PRIMARY INDEX and PRIMARY KEY are specified in a CREATE TABLE statement, then the PRIMARY InDeX is the hashing index and the PRIMARY KEY is mapped to a unique secondary index (USI).

If a PRIMARY KEY is specified in a CREATE TABLE statement and the PRIMARY INDEX is not, then the PRIMARY KEY is mapped to unique primary index (UPI).

If neither PRIMARY INDEX nor PRIMARY KEY is specified in a CREATE TABLE statement then the first UNIQUE constraint is considered as the UNIQUE PRIMARY INDEX (UPI).

If there is no PRIMARY INDEX, PRIMARY KEY, or UNIQUE constraint specified in a CREATE TABLE statement, then the first column of the table is used as the primary index. This index is non-unique except for the case of a single column table with the SET option.

Columns defined as PRIMARY KEY or UNIQUE, must not be nullable. However, columns defined as [UNIQUE] PRIMARY INDEX or [UNIQUE] INDEX can be nullable.

Teradata RDBMS for UNIX SQL Reference

8-85
Teradata SQL Syntax Guide

CREATE TABLE

Nullable Columns are Accepted in UNIQUE [PRIMARY] INDEXes

DATABLOCKSIZE

It is not permitted to define a PRIMARY KEY or UNIQUE constraint with the same column or set of columns, as a defined INDEX. A UNIQUE constraint cannot be defined on the same columns as a PRIMARY KEY.

Columns defined as primary keys or unique must not be nullable. However, nullable columns are accepted in UNIQUE [PRIMARY] INDEXes.

If an index declared as a UNIQUE INDEX or UNIQUE PRIMARY INDEX consists of a single column, only one row can exist in the table with a null value for that column. If an index declared as a UNIQUE index or UNIQUE PRIMARY index consists of several columns the table cannot have more than one row in which all of the non-null values are identical.

DATABLOCKSIZE controls the value of the data block size attribute, as follows:

If BYTES is specified, the value of the data block size attribute is the result of rounding n to the nearest multiple of the sector size (in bytes), where the rounding is upward in the case that n is exactly halfway between two consecutive multiples.

A sector is 512 bytes. If the computed value for the data block size attribute does not fall within the allowable range (6144 to 32256 bytes), an error message is returned.

If KILOBYTES is specified, the data block size attribute is the result of rounding 1024n (instead of n) in the same manner.

In either case, the attribute value will determine the maximum size of multiple-row data blocks in the table.
Previous << 1 .. 128 129 130 131 132 133 < 134 > 135 136 137 138 139 140 .. 241 >> Next