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 .. 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.

2 - 64 Teradata RDBMS Database Administration

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

IF you want more information on . THEN see .
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

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

IF you want more information on . THEN see .
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.

2 - 64 Teradata RDBMS Database Administration

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