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


When the query used in example 1A is resubmitted, EXPLAIN shows that the Optimizer would employ j1_test as a partial-covering join index to resolve the query.

The Optimizer can use the base table USI, j1, to join j1_test back to t1, as shown in the following example:

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

EXPLAIN SELECT * FROM t1 WHERE i1=2;

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

Explanation

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 a single-AMP RETRIEVE step from MVIEW.ji_test by way of the

primary index "MVIEW.ji_test.i1 = 2" with no residual conditions 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 1 row. The estimated time for this step is 0.03 seconds.

4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an

all-rows scan, which is joined to MVIEW.t1 by way of a traversal of index # 8 extracting row ids only. Spool 2 and MVIEW.t1 are joined using a nested join, with a join condition of ("Field_1026 = MVIEW.t1.j1"). The input table MVIEW.t1 will not be cached in memory. The result goes into Spool 3, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by field Id 1. The size of Spool 3 is estimated with index join confidence to be 1 row. The estimated time for this step is 0.15 seconds.

5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an

all-rows scan, which is joined to MVIEW.t1. Spool 3 and MVIEW.t1 are joined using a row id join, with a join condition of ( "Field_2 = MVIEW.t1.j1"). The input table MVIEW.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 index join confidence to be 1 row. The estimated time for this step is 0.28 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 0.46 seconds.

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Example 2A - Selective Constraint Using Partial-Covering Join Index

In this example, the following table and join index definitions are used to prototype the use of a single-table, partial-covering join index:

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 following test query was submitted:

EXPLAIN SELECT x1,y1,z2 FROM t1,t2 WHERE y1=x2 AND y2 = 1;

Note that in this query, the expression t2.y2=1 is a very selective constraint. Joining t1 and t2 requires redistribution of all the t1 rows by t1.y1.

This can be extremely expensive if t1 is a very large table. An alternative is to use the join index defined above to:

Do a local join between j1 and t2

Redistribute the join result

Join back to t1

The following EXPLAIN on the example query shows that the Optimizer would use index j1 to process the t1, t2 join.

This proves that redistributing the join result of j1 and t2 and doing a join-back to t1 is less expensive than redistributing all the rows of t1.

explain sel x1,y1,z2 from t1,t2 where y1=x2 and y2 = 1;

*** Help information returned. 29 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.j1.

2) Next, we lock a distinct STJI."pseudo table" for read on a RowHash

to prevent global deadlock for STJI.t2.

3) We lock a distinct STJI."pseudo table" for read on a RowHash to

prevent global deadlock for STJI.t1.

4) We lock STJI.j1 for read, we lock STJI.t2 for read, and we lock

STJI.t! for read.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

5) We do an all-AMPs JOIN step from STJI.t2 by way of a RowHash match

scan, with a condition of ("STJI.t2.y2 = 1"), which is joined to STJI.j1. STJI.t2 and STJI.j1 are joined using a merge join, with a join condition of ("STJI.j1.y1 = STJI.t2.x2"). The input table STJI.j1 will not be cached in memory. The result goes into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by field Id 1. The size of Spool 2 is estimated with no confidence to be 1 row. The estimated time for this step is 0.06 seconds.

6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
Previous << 1 .. 41 42 43 44 45 46 < 47 > 48 49 50 51 52 53 .. 218 >> Next