Download (direct link):
2 To determine whether the optimizer will use the join index in processing your applications, submit typical queries as follows:
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 AdministrationChapter 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
• UPI or UPPI I
• 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:
EXPLAIN SELECT * FROM t1 WHERE i1 = 2;
*** 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:
CREATE JOIN INDEX ji_test AS SELECT i1,j1 FROM t1;