 |
 |
Archives of the TeradataForum
Message Posted: Mon, 21 Jan 2002 @ 14:40:54 GMT
| Subj: | | Re: Optimize Spool Space via View Creation? |
| |
| From: | | Rudel Simard |
You have to be very carefull about the join not needed in your view by the SELECT. It doesn't use the spool but take time to execute the
query.
I will try to explain the situation with a small exemple :
CREATE VIEW SYSDBA.VIEW1 AS
SELECT T1.A,T1.B, T2.C,T2.D,T3.E,T3.F,T4.G,T4.H,T5.I,T5.J,T6.K,T6.L
FROM SYSDBA.T1 LEFT JOIN SYSDBA.T2 ON
T1.A = T2.C
LEFT JOIN SYSDBA.T3 ON
T2.C = T3.E
LEFT JOIN SYSDBA.T4 ON
T3.E = T4.G
LEFT JOIN SYSDBA.T5 ON
T4.G = T5.I
LEFT JOIN SYSDBA.T6 ON
T5.I = T6.K;
EXPLAIN
SELECT B,D
FROM SYSDBA.VIEW1 ;
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct SYSDBA."pseudo table" for read on a RowHash to prevent global deadlock for SYSDBA.T6.
| |
| | 2) | Next, we lock a distinct SYSDBA."pseudo table" for read on a RowHash to prevent global deadlock for SYSDBA.T5.
| |
| | 3) | We lock a distinct SYSDBA."pseudo table" for read on a RowHash to prevent global deadlock for SYSDBA.T4.
| |
| | 4) | We lock a distinct SYSDBA."pseudo table" for read on a RowHash to prevent global deadlock for SYSDBA.T3.
| |
| | 5) | We lock a distinct SYSDBA."pseudo table" for read on a RowHash to prevent global deadlock for SYSDBA.T2.
| |
| | 6) | We lock a distinct SYSDBA."pseudo table" for read on a RowHash to prevent global deadlock for SYSDBA.T1.
| |
| | 7) | We lock SYSDBA.T6 for read, we lock SYSDBA.T5 for read, we lock SYSDBA.T4 for read, we lock SYSDBA.T3 for read, we lock SYSDBA.T2 for
read, and we lock SYSDBA.T1 for read.
| |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from SYSDBA.T3 by way of a RowHash match scan with no residual conditions, which is joined to
SYSDBA.T4. SYSDBA.T3 and SYSDBA.T4 are left outer joined using a merge join, with a join condition of ("SYSDBA.T3.E = SYSDBA.T4.G"). The
result goes into Spool 2, which is built locally on the 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 22 rows. The estimated time for this step is 0.06 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from SYSDBA.T5 by way of a RowHash match scan with no residual conditions, which is joined to
SYSDBA.T6. SYSDBA.T5 and SYSDBA.T6 are left outer joined using a merge join, with a join condition of ("SYSDBA.T5.I = SYSDBA.T6.K"). The
result goes into Spool 3, which is duplicated on all AMPs. Then we do a SORT to order Spool 3 by row hash. The size of Spool 3 is
estimated with low confidence to be 264 rows. The estimated time for this step is 0.06 seconds.
| | |
| | |
| | 3) | We do an all-AMPs JOIN step from SYSDBA.T1 by way of a RowHash match scan with no residual conditions, which is joined to
SYSDBA.T2. SYSDBA.T1 and SYSDBA.T2 are left outer joined using a merge join, with a join condition of ("SYSDBA.T1.A = SYSDBA.T2.C"). The
result goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is
estimated with low confidence to be 22 rows. The estimated time for this step is 0.06 seconds.
| | |
| | 9) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to Spool 3 (Last Use). Spool 2
and Spool 3 are left outer joined using a merge join, with a join condition of ("G = I"). The result goes into Spool 5, which is duplicated
on all AMPs. Then we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated with low confidence to be 1,944 rows. The
estimated time for this step is 0.08 seconds.
| |
| | 10) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 (Last Use). Spool 4
and Spool 5 are left outer joined using a merge join, with a join condition of ("C = E"). The result goes into Spool 1, which is built
locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 1,188 rows. The estimated time for this step is 0.20
seconds.
| |
| | 11) | 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.34 seconds.
| |
Rudel Simard
R�gie de l'assurance-maladie du Qu�bec
DBA de l'environnement informationnel
| |
