in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 40 41 42 43 44 45 < 46 > 47 48 49 50 51 52 .. 218 >> Next

2 To determine whether the optimizer will use the join index in processing your applications, submit typical queries as follows:
Step Action
a With no indexes defined, use BTEQ scripts (or other tool) to submit typical queries against the involved table or tables, being sure to precede each query with the EXPLAIN modifier.
b Create the join index or indexes you want to prototype. Each join index can be a single-table projection or a multi-table join, and can carry an SI. (If you want a NUSI to cover the join index, investigate the ALL option; see "CREATE [JOIN] INDEX" in Chapter 1 of Teradata RDBMS SQL Reference, Volume 4.)
c For each join index you create, submit COLLECT STATISTICS against these, and only these, column or columns: The NUPI (if you did not define a NUPI, the default is the first column; the PI of a join index is never unique). Any NUSI
d Resubmit the same set of queries, preceded with the same EXPLAIN modifiers as before.
e Compare the EXPLAIN output generated before and after you created the join indexes. If a join index is not included in the processing plan, review the rules of use to see whether the index definition and structure are suitable for the application.

3 For best results, keep statistics current on any NUSI columns of join indexes and the PI, SI, and join or JI columns of frequently-joined production tables.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Using a Partial-Covering Join Index

The Optimizer can use a partial-covering index when it can join the index with the base table or tables to find the remaining columns that satisfy the query.

To qualify for use as partial-covering, the join index must:

Define only a subset of the columns needed to satisfy the query

Reference each base table using a unique identifier. This must be at least one | of the following:

ROWID keyword


NUPI or NUPPI, along with one of the following: |

- ROWID (preferred)


The method used to join the base table with a partial-covering index depends on the join index definition, as follows:

IF CREATE JOIN INDEX statement includes . THEN .
only the rowID of the base table or tables a Rowid Join joins the partial-covering index back to its base table or tables.
the PI or SI of the base table or tables a Merge Join uses the PI and the rowID, if present, to join the partial-covering index back to its base table or tables. Whether the rowID is required in the join index definition depends on the type of primary index:

IF the base table index is . . . THEN . . .
unique (UPI) ROWID is not necessary in the join index definition.
non-unique (NUPI) the join index definition must include a unique identifier, either ROWID or USI.

Prototyping a Single-Table, Partial-Covering Index

When a join query would benefit from having one of the columns of its joining table hash on the join key rather than the primary index, then you should consider using a single-table join index.

Benefits and suggestions are discussed under "Single-Table Join Indexes" in the chapter titled "Join and Hash Indexes" in Teradata RDBMS Database Design. To illustrate the point, the following examples use EXPLAIN to prototype a single-table, partial-covering join index.

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Example 1A - SELECT on a Table Without a Join Index

This first example uses the following table definition:

CREATE TABLE t1 (k1 int, i1 int, j1 int) PRIMARY INDEX (k1), UNIQUE INDEX (j1);

When no join index is defined on this table, EXPLAIN shows how a constrained SELECT statement is processed:


*** Help information returned. 14 rows. *** Total elapsed time was 1 second.


1) First, we lock a distinct MVIEW."pseudo table" for read on a

RowHash to prevent global deadlock for MVIEW.t1.

2) Next, we lock MVIEW.t1 for read.

3) We do an all-AMPs RETRIEVE step from MVIEW.t1 by way of an

all-rows scan with a condition of ("MVIEW.t1.i1 = 2") into Spool 1, which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with low confidence to be 1 row. The estimated time for this step is 18 minutes and 52 seconds.

4) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 18 minutes and 52 seconds.

Example 1B - SELECT on a Table With a Partial-Covering Join Index

In this example a single-table, partial-covering join index, j1_test, is defined on t1, using both the NUPI and the USI from t1:

Previous << 1 .. 40 41 42 43 44 45 < 46 > 47 48 49 50 51 52 .. 218 >> Next