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 Database Administration - NCR

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


During query processing, the Optimizer estimates comparative costs to determine whether the use of an SI offers savings or is plausible. If not, a full-table scan is used.

To achieve the best performance, therefore, it is good practice to test each SI before going into full production. To do this, you can:

Emulate your production environment on a separate OS using TLE (see "Target Level Emulation (TLE)" on page 13-3).

Generate a description of the processing sequence, which helps determine whether an existing SI should be retained or changed, using the:

SQL EXPLAIN modifier (see "SQL Data Manipulation Language Statement Syntax" in Teradata RDBMS SQL Reference, Volume 6)

Teradata Visual Explain utility (see "Teradata Visual Explain Utility" on page 13-12)

Teradata Index Wizard, if you implement QCF (see "Teradata Index Wizard" on page 13-10).

2 - 22

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

Join Indexes (JI)

A JI represents a join result or a single-table projection. When a JI can be used to fully satisfy a query, you avoid the need to access multiple base tables, perform joins or aggregates, and/or redistribute the rows of very large tables, every time the query is run. You can create JIs for the following functionality:

Single table or multi-table

partial-covering or covering

Simple or aggregate

Stored in hash or value order

Note: If a partial-covering join index is used to resolve a query, the join back to the base table is subtracted from the per-query maximum.

A JI table is hash-distributed, with possibly a different distribution of rows to the AMPs and row ordering on the AMPs from the base table rows.

You can define one or more JIs on any table that does not already have triggers. Also, you can define one or more SIs on a JI. If you want a NUSI on a join index to completely cover a query, use the ALL option.

You can also specify a ROWID column in a join index definition. ROWID is a system-derived column that provides the internal row identifier associated with a row of a table. The principle use for ROWID values is to retrieve rows. This application is useful for enabling non-covering or partial-covering join indexes to join with base table columns

If you change the partitioning expression for the PI of a table with ALTER TABLE, the ROWID values for that table may change.

For more details, see "CREATE JOIN INDEX" in Teradata RDBMS SQL Reference, Volume 4.

Also, if you want to define multiple join and/or hash indexes on a PPI table, be sure to plan for the increase in memory consumption as explained under "Multiple Join Indexes and Memory Considerations" on page 2-39.

Hash Index (HI)

A hash index is a special form of single-table join index that is also useful for covering queries, but a hash index cannot carry an SI.

The default for storing a hash index is to compress its values, unless at least one of the following is true:

ORDER BY is defined on an index column that is not the first column

The first column defined in the CREATE HASH INDEX statement (which becomes the PI of the index table) is the UPI column of the base table

2 - 22 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

Keep the following in mind when defining a hash index:

IF . THEN . For more details, see .
a hash index qualifies as partial-covering and is used to resolve a query each join back to the base table is subtracted from the 64 joins per query maximum. "Hash and Join Index Storage" in the chapter "Join and Hash Indexes" in Teradata RDBMS Database Design "CREATE HASH INDEX" in Teradata RDBMS SQL Reference, Volume 4
you omit the ORDER BY clause in a CREATE HASH INDEX statement the index rows will default to the same rowhash value as the base table rows. Thus, the hash index rows will be AMP-local with the those of its base table and so will provide faster access. Note: For a PPI table, include ORDER BY (see "Secondary Indexes (SI)" on page 2-35).
the columns of the order key are not unique Teradata automatically compresses rows having the same order-key values into a single physical row having fixed and repeating parts, as follows: The fixed part is made up of the columns that define the order key The repeating part is made up of the remaining columns If all the columns do not fit into a single physical row, additional physical rows are allocated as necessary.
you want to define an HI (and/or multiple join indexes) on a PPI table plan for the increase in memory consumption as explained under "Multiple Join Indexes and Memory Considerations", below. "Memory Limitations Related to Partitioned Primary Indexes" in: Teradata RDBMS Database Design Teradata RDBMS SQL Reference, Volume 4

2 - 22

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)
Previous << 1 .. 34 35 36 37 38 39 < 40 > 41 42 43 44 45 46 .. 218 >> Next