 |
 |
Archives of the TeradataForum
Message Posted: Mon, 15 Aug 2005 @ 09:08:27 GMT
| Subj: | | Re: ROWID based join! |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Monday, August 15, 2005 02:08 -->
1. It is a valid query and works both in ANSI & BTET, but the result doesn't provide the information that it is being grouped by dept.
2. You could refer in the SQL manuals->Statement and transaction processing.
A rowid join is a join between 2 tables (here left table is usally a spool file) where each Left row is read and the associated right row is
read using the rowid in the left row and the join result is produced. Continue until no rows in the left table. The left table here would mostly
be a resultant spool file from a nested join.
Another case where rowid join is used is when you have a join index which fully doesn't cover the query, but it has the rowid of the base
table. To get the non-covering values, we use a rowid join between the base table and the join index on which it is defined to get the other
rows.
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 j1 as sel y1, rowid from t1 primary index (y1);
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.t1 for read.
| |
| | 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 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.
| |
| |
