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 .. 23 24 25 26 27 28 < 29 > 30 31 32 33 34 35 .. 76 >> Next


• Locate data rows

• Improve performance (indexed access is often more efficient than searching all rows of a table)

• Ensure uniqueness of the index values (only one row of a table can have a particular value in the column (s) defined as a unique index)

A table must have one primary index and none, one or several secondary indexes.

You cannot force the optimizer to use any index—it selects whichever index or indexes will return the query result most quickly. In some cases, the optimizer will process the query without using any index.

Selection of indexes:

• can have a direct impact on overall Teradata performance

• is not always a straight forward process

• is based partly on usage information

Logically, an index consists of two values: a data value and a pointer to a data row. In the case of non-unique indexes, a data value can point to one or more data rows.

Introduction to the Teradata RDBMS for UNIX

4-9
Data Definition

Indexes

Getting Ideas for Where and When to Index

Primary Index

The primary index is defined at the time the table is created. The primary index affects system performance in the following ways:

• The hash value of the primary index values determine the distribution of rows on disk.

• When the value of a primary index column is specified in an equality constraint, the hash value is used to directly access the applicable row.

When a new row is inserted into a table, a hashing algorithm is applied to the primary index value. The hash result determines on which disk the row will be stored.

Secondary Index

Secondary indexes allow access to information in a table by alternate, less frequently used paths.

Secondary indexes require the maintenance of a subtable and the associated overhead (additional storage space and maintenance.)

Join Index

A join index is an indexing structure containing columns from multiple tables, specifically the resulting columns from one or more tables.

Join Index was developed so that frequently executed join queries could be processed more efficiently. Rather than having to join individual tables each time the join operation is needed, the query can be resolved via a join index subtable and, in most cases dramatically improve performance.

For more details on join index see Chapter 16, “Indexing Tables,” in the Teradata RDBMS for UNIX Database Design and Administration Manual. For information on the join index syntax structure, see the Teradata RDBMS for UNIX SQL Reference.

There are several things you can do to help determine what columns to index.

• Run EXPLAINs on typical queries with and without indexes defined on various columns to determine which performs best.

• Run HELP INDEX tablename statements to produce information helpful for interpreting the EXPLAIN statements you run.

• Run COLLECT STATISTICS on the tables to be indexed to provide data for assessing the cost/benefit balance afforded by indexes.

4-10

Introduction to the Teradata RDBMS for UNIX
Data Definition

Indexes

Primary Indexes vs. Secondary Indexes

IF there . . . THEN make . . .
is only one frequently used set selection it the primary index if it is unique or nearly unique.
are two frequently used set selections, one of which is unique and the other of which is very selective the unique set selection a Unique Secondary Index (USI) and make the highly selective set selection a Non-Unique Primary Index (NUPI).

The following table details facts comparing primary and secondary indexes for the Teradata RDBMS.

Index Primary Secondary
Required Yes No
Number per table 1 0-32
Affects row distribution Yes No
Affects performance Yes Yes
Number of columns 1-16 1-16
Multiple data types Yes Yes
Unique UPI USI
Nonunique NUPI NUSI
Unique vs. nonunique Function similarly Function differently
Subtable required No Yes
Extra processing overhead No Yes
Define in CREATE TABLE Yes Yes
Define in CREATE INDEX No Yes
Drop index No Yes

The following table summarizes the number of AMPs and results rows used by each index access method.

Access Method Number of AMPs Used Number of Results Rows Returned
UPI 1 1
NUPI 1 1 - many
USI 2 1

Introduction to the Teradata RDBMS for UNIX

4-11
Data Definition

Indexes

Access Method Number of AMPs Used Number of Results Rows Returned
NUSI all 1 - many
Full table scan all 1 - all

It is difficult to provide concrete recommendations for using indexes or combinations of indexes because their usefulness depends so highly on individual tables and their application.

The following table explains the strengths and weaknesses of the various access methods.

Access Method Comments
UPI • Very efficient • One AMP, one row • No spool file
NUPI • Efficient when the number of rows per value is fewer than 100 • One AMP, multiple rows • May require spool file
USI • Very efficient • Two AMPs, one row • No spool file
NUSI • Efficient only when the number of rows accessed is fewer than the number of data blocks in the table. • All AMPS, multiple rows • May require spool file
Previous << 1 .. 23 24 25 26 27 28 < 29 > 30 31 32 33 34 35 .. 76 >> Next