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 .. 39 40 41 42 43 44 < 45 > 46 47 48 49 50 51 .. 218 >> Next

4 Submit the queries to be tested, preceding each with the EXPLAIN modifier.
5 If you plan to define secondary or join indexes on some or all of the tables, create the indexes now (using the statements introduced in "Creating and Modifying Indexes" on page 2-40).

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Step Action
6 Submit SQL COLLECT STATISTICS (Optimizer form) statements on:
All SI columns
The PI columns of all join indexes
Any joining columns that are not part of any index
Any other columns, partitioned or not, commonly used for qualifying
access
7 Resubmit your suite of queries to obtain a comparative set of EXPLAIN plans.

Prototyping a Primary Index

This section provides an overview to prototyping primary and secondary indexes, using a PI as an example. You cannot add, drop, or modify the PI of a populated table. You must recreate it (see "Recreating a Table" on page 2-9).

Use the following methods to test and compare your PI choice:

Analyze row distribution

Analyze performance

Analyzing Row Distribution

Using a UPI or UPPI should greatly reduce the number of collisions (hash code | duplications). This provides for even row distribution across the AMPs, taking best advantage of Teradata RDBMS parallel processing capability.

If you use a NUPI or NUPPI, the Teradata HASHROW function can help you | determine the number of collisions your indexed data generates. You can use the HASHROW, HASHBUCKET, and HASHAMP functions together to determine whether a PI provides an even distribution of rows:

The function . Returns the .
HASHROW hash ID of each row or set of rows, when applied to the column or columns making up an index.
HASHBUCKET bucket number that corresponds to a hashed (PI) row.
HASHAMP AMP ID that corresponds to a hash bucket number.

For more information on using the HASH functions to investigate performance, see Teradata RDBMS SQL Reference, Volume 5 and Teradata RDBMS Performance Optimization.

If you did not define a column named PARTITION for a table, you can select the system-derived PARTITION column to view the external partition number for a row. For details, see "System-Derived PARTITION Column" on page 2-31.

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

You can check the table's space utilization on all AMPs by selecting tahlename from the DBC.TableSize view as shown in "TableSize View" on page 3-15.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Analyzing PI Performance

Step Action
1 Create two tables, identical except for the definition of the PI. The difference can be unique or not and/or partitioned or not and/or the column set.
2 Use BTEQ scripts to run typical queries against the test data. To obtain accurate EXPLAIN output, enter COLLECT STATISTICS on the PI of each table. Include enough queries to obtain a broad comparison sampling.
Step Action
a Precede each test query with the EXPLAIN modifier and submit them against the table with one PI or PPI definition.
b Submit the same group of queries, again including EXPLAIN, against the table with the other PI or PPI definition.
c Compare the two EXPLAIN outputs to determine how each index is used and if there is any difference in estimated processing time.

3 Drop the table with the least efficient primary index and keep the remaining table as your production data table.
4 For best results, keep statistics current on frequently-used indexes and tables.

Analyzing Secondary Indexes with the Teradata Index Wizard

When your database is in production, chances are that you would like to analyze and improve the performance of your various applications.

The Teradata Index Wizard eases the process for you by automatically analyzing SI use based on data demographics. To do this:

Perform index analysis on the defined workload in order to obtain the index recommendations.

Validate the index recommendations on the production system.

Apply the index recommendations on the system.

For more details, see:

"Query Capture Facility (QCF)" on page 13-4

In Teradata RDBMS SQL Reference, Volume 2:

"Query Capture Feature"

"Database Foundations for the Teradata Index Wizard"

Teradata Index Wizard User Guide

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Prototyping Join Indexes

An index is used only when the Optimizer can determine a cost savings. Prototyping can help you develop useful index definitions.

Step Action
1 To obtain accurate EXPLAIN output, enter a COLLECT STATISTICS (Optimizer form) statement on the following columns of the base table or tables PI columns, SI columns, and joining columns.
Previous << 1 .. 39 40 41 42 43 44 < 45 > 46 47 48 49 50 51 .. 218 >> Next