Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Previous << 1 .. 42 43 44 45 46 47 < 48 > 49 50 51 52 53 54 .. 218 >> Next

all-rows scan, which is joined to STJI.t1. Spool 2 and STJI.t1 are joined using a row id join, with a join condition of ( "Field_1 = STJI.t1.RowID"). The input table STJI.t1 will not be cached in memory. The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 1 row. The estimated time for this step is 0.20 seconds.

7) 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 0.26 seconds.

Example 2B - Constraint Does Not Use Partial-Covering Join Index

To see how constraints affect the use of the partial-covering join index, we use the same table and join index created in example 2A:

CREATE TABLE t1 (x1 int, y1 int, z1 int) PRIMARY INDEX (x1); CREATE TABLE t2 (x2 int, y2 int, z2 int) PRIMARY INDEX (x2); CREATE JOIN INDEX stjitest1 AS SEL y1, ROWID FROM t1 PRIMARY INDEX (y1);

The query is similar to example 2A, but lacks the "t2.y2=1" constraint:

EXPLAIN SELECT x1,y1,z2 FROM t1,t2 WHERE y1-x2; The EXPLAIN output shows that now j1 is not used:

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

1) First, we lock a distinct STJI."pseudo table" for read on a RowHash to prevent global deadlock for STJI.t2.

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

2) Next, we lock a distinct STJI."pseudo table" for read on a RowHash to prevent global deadlock for STJI.t1.

3) We lock STJI.t2 for read, and we lock STJI.t1 for read.

4) We do an all-AMPs RETRIEVE step from STJI.t1 by way of an all-rows scan with a condition of ("NOT (STJI.t1.y1 IS NULL)") into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with low confidence to be 101 rows. The estimated time for this step is 3 minutes and 9 seconds.

5) We do an all-AMPs JOIN step from STJI.t2 by way of a RowHash match scan., which is joined to Spool 2 (Last Use). STJI.t2 and Spool 2 are joined using a merge join, with a join condition of ("y1 = STJI.t2.x2"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 3 rows. The estimated time for this step is 0.18 seconds.

6) 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 3 minutes and 9 seconds.

Documentation References

the syntax for creating tables and indexes and collecting column, random, and sampled statistics • "COLLECT STATISTICS (Optimizer Form) in Teradata RDBMS SQL Reference, Volume 4
the design, use, costs, and performance benefits of indexes with Teradata RDBMS tables • The chapter titled "Teradata Indexes: The Basics" in Teradata RDBMS Database Design • Teradata RDBMS SQL Reference, Volume 1 • Teradata RDBMS Performance Optimization
how to optimize performance with indexes the chapter titled "Query Optimization" in Teradata RDBMS SQL Reference, Volume 2
prototyping and analyzing the use of indexes with EXPLAIN the section titled "Using EXPLAIN Statements to Determine the Usefulness of Indexes" under "Teradata Indexes: The Basics" in Teradata RDBMS Database Design

2 - 64

Converting Non-Relational Data Structures

using the Query Capture Feature (QCF) form of COLLECT STATISTICS and for analysis of EXPLAIN output • "Teradata SQL Statements for Query Analysis" on page 13-6 • In Teradata RDBMS SQL Reference, Volume 2: - "COLLECT STATISTICS (QCF form) - The chapter titled "Query Capture Facility"
logging the text and processing behavior of a statement Chapter 14: "Tracking Processing Behavior with the Database Query Log (DBQL)"
operation of FastLoad, MultiLoad, or Teradata TPump the corresponding reference document in the suite of client documents.

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Converting Non-Relational Data Structures

This section provides information on Teradata RDBMS data and data structures. It includes the following topics:

• Changing data representation from your old system to the Teradata form

• Converting non-relational to relational data structures

Changing Data Representation

When a database is restructured in some database management systems, application programs that use these structures must be modified. This is unnecessary when data in a Teradata RDBMS database is changed.

After restructuring, applications that use a Teradata RDBMS preprocessor can continue to access the changed data by using the feature that converts data as it is moved into program variables.
Previous << 1 .. 42 43 44 45 46 47 < 48 > 49 50 51 52 53 54 .. 218 >> Next