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

Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 25 26 27 28 29 30 < 31 > 32 33 34 35 36 37 .. 76 >> Next

UPDATE... WHERE Name = 'Leidner P'; Non-fallback table No unique index fields Involves two AMPs Step is first sent to index AMP Step is forwarded to primary AMP Involves all AMPs Reduces the number of rows examined on each AMP
INSERT... 'Leidner P'; Non-fallback table Same rules hold for an UPDATE on the same table Involves two AMPs Secondary index subtable is added to an AMP other than the primary AMP and the operation fails if the secondary index value is already in use. Involves one AMP Secondary index subtable rows are added or deleted on the same AMP

4-16

Introduction to the Teradata RDBMS for UNIX
Data Definition

Secondary Indexes

Unique Secondary Index

Nonunique Secondary Index

Guidelines for Selecting a Secondary Index

A unique secondary index (USI) associates one subtable row with each base table data row.

In general, use a USI on the primary key column whenever your primary index is nonunique. This makes row updates and deletions more efficient.

USIs are also useful for enhancing the performance of nested joins.

The subtable for a USI is usually stored on a different AMP from the base table it indexes.

Non-unique secondary indexes (NUSIs) are maintained as separate subtables on each AMP. The index subtable entries point to base table rows that reside on the same AMP as the index subtable.

NUSIs are useful for situations in which a full table scan would otherwise occur, for example when you need to locate rows with a particular value or set of values. Though such an operation still involves all AMPs, a properly selected NUSI limits the number of rows that need to be processed.

Teradata RDBMS has been enhanced in two important ways:

• Support for the user-specified option of sorting the index rows by data value rather than by the corresponding hash code. This is particularly useful for range queries where only a portion of the index subtable will be accessed.

• Improvements have been made to use NUSIs to “cover” queries, that is, to avoid accessing the data rows altogether for those queries that reference only indexed columns.

Keep these guidelines in mind when you select a secondary index

for your tables.

• The column (or column set) chosen should be a frequently used set selection.

• Selection should be based on an equality search.

• The data values for the index should not be subject to frequent changes because of the overhead involved in updating subtables.

Introduction to the Teradata RDBMS for UNIX

4-17
Data Definition

Creating an Index for a Table

Introduction

Creating a Primary Index

Creating a Secondary Index

Rule of Thumb for Creating Indexes

Creating an Index for a Table

Database systems use indexes to facilitate quick selection of rows. Unlike other database management systems, relational systems typically do not permit explicit use of indexes in application programs or queries. Instead, the optimizer decides at the moment of SQL compilation which index or indexes (if any) to use to optimize the query.

This topic describes how to use SQL to create an index on a table.

All tables require a primary index. If you do not specify a column or group of columns to act as a primary index for a table when you create it, the system defaults to the first column you define. Because this could lead to uneven distribution of data, you should choose the primary index for your table carefully.

You create a unique primary index for a table using the UNIQUE PRIMARY INDEX clause of the CREATE TABLE statement. You cannot change the primary index for a table.

To create a nonunique primary index, drop the keyword UNIQUE or use the PRIMARY INDEX clause in the CREATE TABLE statement.

Secondary indexes are always optional.

To create a unique secondary index, use the CREATE UNIQUE INDEX statement.

For example,

CREATE UNIQUE INDEX (cust)

ON CUSTOMER

;

To create a nonunique secondary index, drop the keyword UNIQUE or use the INDEX clause of the CREATE TABLE statement.

A general rule of thumb to observe when creating indexes is this: if the primary key for your table is not the unique primary index, then make it a unique secondary index.

4-18

Introduction to the Teradata RDBMS for UNIX
Data Definition

Dropping Tables and Indexes

Dropping Tables and Indexes

Introduction

This topic describes how to use Teradata SQL to drop indexes and tables from a database.

Dropping a Table

The following example drops a table named Employee from the database.

DROP TABLE Employee;

Dropping an Index

The following example drops an index on the Name column from the Employee table. Note that this action does not remove the Name column, only its use as an index for the table.

DROP INDEX (Name) ON Employee;

Introduction to the Teradata RDBMS for UNIX

4-19
Data Definition

For More Information

For More Information

For more information on the topics presented in this chapter, see the following Teradata RDBMS manuals.
Previous << 1 .. 25 26 27 28 29 30 < 31 > 32 33 34 35 36 37 .. 76 >> Next