Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 24 Aug 2006 @ 10:22:26 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Weird Behaviour of SQL in Teradata
 
From:   Anomy Anom

<-- Anonymously Posted: Thursday, August 24, 2006 06:13 -->

Hi all

It is a little bit weird that the same query with a different IN condition provides me with different result set ..

Pls find the SQL below . I have highlighted the part in RED where it gives me error..If I use the condition highlighted in BLUE it works fine ..

Pls let me know if any more details are required ..

I have also placed the explain plan of the queris for all three types of execution highlighted (Explain of Red / Explain of Blue Line 1 /Explain of Blue Line 2)


     sel c.CA_Cross_Sell_Group As Pdt_Group,
     Count(Distinct a.main_acct_holder_party_id) #Customers,
     Count(Distinct a.account_num||a.account_modifier_num) #Placements,
     Sum(v0521.Event_Amt*cast(x_rate.global_to_source_currency_rate As
     decimal(10,5))) Sum_Rollover_Amt_SGD,
     Avg(v0521.Event_Amt*cast(x_rate.global_to_source_currency_rate As
     decimal(10,5))) Avg_Rollover_Amt_SGD
      from
     (sel Account_Num,
     Account_Modifier_Num,
     Main_Acct_Holder_Party_Id
      from          VM_ME_TERM_DEPOSIT
     Where      Period_ID='2006-07-31'
             And     Last_Renewal_Dt>='2006-07-01'
             And     Last_Renewal_Dt<='2006-07-31') a
     Join
     (
     Select  v0300.account_num,
     v0300.account_modifier_num,b.ca_cross_sell_group
      from
     V0300_AGREEMENT v0300
     Join V0200_MAP v0200
             On      v0200.EDW_Value=v0300.Product_Id
     Join dp_sb_ca.pdt_group b
             On      v0200.Source_Value=b.host_prod_id
             And     v0200.Source_Number=b.Source_Number
     Where      CA_Cross_Sell_Group In ('ACU Fixed Deposit','DBU Fixed
     Deposit')   /* Doesn't Complete ,, Gives Error No More Spool Space */
     -- Where           CA_Cross_Sell_Group In ('ACU Fixed Deposit','DBU
     Fixed Deposit','' )   /* Works Fine  answer is in 2 mins*/
     -- Where           CA_Cross_Sell_Group In ('ACU Fixed Deposit','DBU
     Fixed Deposit','Structured Deposit' )   /* Works Fine  answer is in 2
     mins*/
     ) c
     on c.account_num = a.account_num
     and c.account_modifier_num = a.account_modifier_num
     Join
     (sel Party_Id, segment_desc
      from          VM_ME_PARTY_INDIVIDUAL
     Where      (segment_desc is Null
             Or        segment_desc='Mass Individual')
             And     period_id='2006-07-31'
     Union
     sel Party_Id, segment_desc
      from          VM_ME_PARTY_ORGANISATION
     Where      (segment_desc is Null
             Or        segment_desc='Mass Individual')
             And     period_id='2006-07-31') party
     On      party.party_id=a.Main_Acct_Holder_Party_Id
     Join
     (sel event_id, account_num, account_modifier_num
      from          V0504_ACCOUNT_EVENT) v0504
             On      v0504.account_num = a.account_num
             And     v0504.account_modifier_num = a.account_modifier_num
     Join
     (sel event_id, event_start_dt, Event_Activity_Type_Cd
      from          V0500_EVENT
     Where      Event_Start_Dt>='2006-07-01'
             And     Event_Start_Dt<='2006-07-31'
             And     Event_Activity_Type_Cd In ('FD000104','ACU00121')) v0500

             On      v0500.event_id = v0504.event_id
     Join
     V0521_FINANCIAL_EVENT v0521
             On      v0521.Event_Id=v0504.event_id
     Join
     dp_sb_ca.x_rate_hist x_rate
             On
     Trim(x_rate.source_currency_cd)=v0521.Event_Currency_Cd
             And     x_rate.period_id = '2006-07-31'
     group by 1

     ***********************************************************************
     Explain OF RED
     1       First, lock dp_sb_ca."pseudo table" for read on a row hash.
     2       Next, we lock dp_sb_ca."pseudo table" for read on a row hash.
     3       We lock DP_FCMDM.DATA_SOURCES_FILTER for access, we lock
     dp_sb_ca.x_rate_hist for read, we lock dp_sb_ca.pdt_group for read, we
     lock DP_TCMDM.TM_ME_PARTY_ORGANISATION for access, we lock
     DP_TCMDM.TM_ME_PARTY_INDIVIDUAL for access, we lock
     DP_TCMDM.TM_ME_TERM_DEPOSIT for access, we lock
     DP_TEDW.T0521_FINANCIAL_EVENT for access, we lock
     DP_TEDW.T0504_ACCOUNT_EVENT for access, we lock DP_TEDW.T0500_EVENT for
     access, we lock DP_TEDW.T0300_AGREEMENT for access and we lock
     DP_TEDW.B0200_MAP for access.
     4       We do an All-AMPs RETRIEVE step from
     DP_TCMDM.TM_ME_PARTY_INDIVIDUAL by way of an all-rows scan into Spool
     10346, which is redistributed by hash code to all AMPs.
     5       We do an All-AMPs RETRIEVE step from
     DP_TCMDM.TM_ME_PARTY_ORGANISATION by way of an all-rows scan into Spool
     10346, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     5       We do an All-AMPs RETRIEVE step from dp_sb_ca.pdt_group by way
     of an all-rows scan into Spool 10350, which is redistributed by hash
     code to all AMPs. This step is performed in parallel.
     5       We do an All-AMPs RETRIEVE step from DP_TCMDM.TM_ME_TERM_DEPOSIT
     by way of an all-rows scan into Spool 10351, which is redistributed by
     hash code to all AMPs. This step is performed in parallel.
     5       We do an All-AMPs RETRIEVE step from
     DP_FCMDM.DATA_SOURCES_FILTER by way of an all-rows scan into Spool
     10353, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     6       We do an All-AMPs RETRIEVE step from Spool 10353 (Last Use) by
     way of an all-rows scan into Spool 10352, which is duplicated on all
     AMPs. This step begins a parallel block of steps.
     6       We do an All-AMPs JOIN step from Spool 10351 (Last Use) by way
     of an all-rows scan, which is joined to table T0300_AGREEMENT. Spool
     10351 and table T0300_AGREEMENT are joined using a merge join . The
     result goes into Spool 10354, which is built locally on the AMPs. This
     step is performed in parallel.
     6       We do an All-AMPs JOIN step from Spool 10350 (Last Use) by way
     of an all-rows scan, which is joined to table B0200_MAP. Spool 10350 and
     table B0200_MAP are joined using a merge join . The result goes into
     Spool 10355, which is redistributed by hash code to all AMPs. This step
     ends a parallel block of steps.
     7       We do an All-AMPs JOIN step from Spool 10352 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10354. Spool 10352 and
     Spool 10354 are joined using a product join . The result goes into Spool
     10356, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     7       We do an All-AMPs RETRIEVE step from dp_sb_ca.x_rate_hist by way
     of an all-rows scan into Spool 10357, which is redistributed by hash
     code to all AMPs. This step is performed in parallel.
     7       We do an All-AMPs RETRIEVE step from
     DP_TEDW.T0521_FINANCIAL_EVENT by way of an all-rows scan into Spool
     10358, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     8       We do an All-AMPs JOIN step from Spool 10357 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10358. Spool 10357 and
     Spool 10358 are joined using a product join . The result goes into Spool
     10359, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     8       We do an All-AMPs JOIN step from Spool 10355 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10356. Spool 10355 and
     Spool 10356 are joined using a product join . The result goes into Spool
     10360, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     9       We do an All-AMPs JOIN step from Spool 10359 (Last Use) by way
     of an all-rows scan, which is joined to table T0504_ACCOUNT_EVENT. Spool
     10359 and table T0504_ACCOUNT_EVENT are joined using a merge join . The
     result goes into Spool 10361, which is redistributed by hash code to all
     AMPs. This step begins a parallel block of steps.
     9       We do an All-AMPs RETRIEVE step from DP_TEDW.T0500_EVENT by way
     of an all-rows scan into Spool 10362, which is built locally on the
     AMPs. This step ends a parallel block of steps.
     10      We do an All-AMPs JOIN step from Spool 10360 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10361. Spool 10360 and
     Spool 10361 are joined using a merge join . The result goes into Spool
     10363, which is redistributed by hash code to all AMPs.
     11      We do an All-AMPs RETRIEVE step from Spool 10346 (Last Use) by
     way of an all-rows scan into Spool 10364, which is redistributed by hash
     code to all AMPs.
     12      We do an All-AMPs JOIN step from Spool 10362 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10363. Spool 10362 and
     Spool 10363 are joined using a merge join . The result goes into Spool
     10365, which is redistributed by hash code to all AMPs.
     13      We do an All-AMPs JOIN step from Spool 10364 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10365. Spool 10364 and
     Spool 10365 are joined using ahash join of 20 partitions. The result
     goes into Spool 10349, which is built locally on the AMPs.
     14      We do a SUM step to aggregate from Spool 10349 by way of an
     all-rows scan. Aggregate Intermediate Results are computed globally,
     then placed in Spool 10370.
     15      We do a SUM step to aggregate from Spool 10370 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10372.
     16      We do an All-AMPs RETRIEVE step from Spool 10372 (Last Use) by
     way of an all-rows scan into Spool 10367, which is built locally on the
     AMPs. This step begins a parallel block of steps.
     16      We do a SUM step to aggregate from Spool 10349 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10376. This step ends a parallel block of
     steps.
     17      We do a SUM step to aggregate from Spool 10376 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10378.
     18      We do an All-AMPs RETRIEVE step from Spool 10378 (Last Use) by
     way of an all-rows scan into Spool 10366, which is built locally on the
     AMPs.
     19      We do an All-AMPs RETRIEVE step from Spool 10366 (Last Use) by
     way of an all-rows scan into Spool 10380, which is redistributed by hash
     code to all AMPs. This step begins a parallel block of steps.
     19      We do an All-AMPs RETRIEVE step from Spool 10367 (Last Use) by
     way of an all-rows scan into Spool 10381, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     20      We do an All-AMPs JOIN step from Spool 10380 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10381. Spool 10380 and
     Spool 10381 are joined using a merge join . The result goes into Spool
     10347, which is built locally on the AMPs.
     21      We send out an END TRANSACTION step to all AMPs involved in
     processing the request.

     ***********************************************************************
     Explain of Blue Line 1


     1       First, lock dp_sb_ca."pseudo table" for read on a row hash.
     2       Next, we lock dp_sb_ca."pseudo table" for read on a row hash.
     3       We lock DP_FCMDM.DATA_SOURCES_FILTER for access, we lock
     dp_sb_ca.x_rate_hist for read, we lock dp_sb_ca.pdt_group for read, we
     lock DP_TCMDM.TM_ME_PARTY_ORGANISATION for access, we lock
     DP_TCMDM.TM_ME_PARTY_INDIVIDUAL for access, we lock
     DP_TCMDM.TM_ME_TERM_DEPOSIT for access, we lock
     DP_TEDW.T0521_FINANCIAL_EVENT for access, we lock
     DP_TEDW.T0504_ACCOUNT_EVENT for access, we lock DP_TEDW.T0500_EVENT for
     access, we lock DP_TEDW.T0300_AGREEMENT for access and we lock
     DP_TEDW.B0200_MAP for access.
     4       We do an All-AMPs RETRIEVE step from
     DP_TCMDM.TM_ME_PARTY_INDIVIDUAL by way of an all-rows scan into Spool
     10385, which is redistributed by hash code to all AMPs.
     5       We do an All-AMPs RETRIEVE step from
     DP_TCMDM.TM_ME_PARTY_ORGANISATION by way of an all-rows scan into Spool
     10385, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     5       We do an All-AMPs RETRIEVE step from DP_TCMDM.TM_ME_TERM_DEPOSIT
     by way of an all-rows scan into Spool 10389, which is redistributed by
     hash code to all AMPs. This step ends a parallel block of steps.
     6       We do an All-AMPs JOIN step from Spool 10389 (Last Use) by way
     of an all-rows scan, which is joined to table T0300_AGREEMENT. Spool
     10389 and table T0300_AGREEMENT are joined using a merge join . The
     result goes into Spool 10390, which is built locally on the AMPs. This
     step begins a parallel block of steps.
     6       We do an All-AMPs RETRIEVE step from
     DP_FCMDM.DATA_SOURCES_FILTER by way of an all-rows scan into Spool
     10392, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     7       We do an All-AMPs RETRIEVE step from Spool 10392 (Last Use) by
     way of an all-rows scan into Spool 10391, which is duplicated on all
     AMPs. This step begins a parallel block of steps.
     7       We do an All-AMPs RETRIEVE step from dp_sb_ca.pdt_group by way
     of an all-rows scan into Spool 10393, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     8       We do an All-AMPs JOIN step from Spool 10393 (Last Use) by way
     of an all-rows scan, which is joined to table B0200_MAP. Spool 10393 and
     table B0200_MAP are joined using a merge join . The result goes into
     Spool 10394, which is redistributed by hash code to all AMPs. This step
     begins a parallel block of steps.
     8       We do an All-AMPs JOIN step from Spool 10390 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10391. Spool 10390 and
     Spool 10391 are joined using an inclusion merge join . The result goes
     into Spool 10395, which is redistributed by hash code to all AMPs. This
     step is performed in parallel.
     8       We do an All-AMPs RETRIEVE step from DP_TEDW.T0500_EVENT by way
     of an all-rows scan into Spool 10396, which is built locally on the
     AMPs. This step is performed in parallel.
     8       We do an All-AMPs RETRIEVE step from dp_sb_ca.x_rate_hist by way
     of an all-rows scan into Spool 10397, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     9       We do an All-AMPs JOIN step from Spool 10396 (Last Use) by way
     of an all-rows scan, which is joined to table T0521_FINANCIAL_EVENT.
     Spool 10396 and table T0521_FINANCIAL_EVENT are joined using a merge
     join . The result goes into Spool 10398, which is redistributed by hash
     code to all AMPs.
     10      We do an All-AMPs JOIN step from Spool 10397 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10398. Spool 10397 and
     Spool 10398 are joined using a product join . The result goes into Spool
     10399, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     10      We do an All-AMPs JOIN step from Spool 10394 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10395. Spool 10394 and
     Spool 10395 are joined using a product join . The result goes into Spool
     10400, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     11      We do an All-AMPs JOIN step from Spool 10399 (Last Use) by way
     of an all-rows scan, which is joined to table T0504_ACCOUNT_EVENT. Spool
     10399 and table T0504_ACCOUNT_EVENT are joined using a merge join . The
     result goes into Spool 10401, which is redistributed by hash code to all
     AMPs.
     12      We do an All-AMPs RETRIEVE step from Spool 10385 (Last Use) by
     way of an all-rows scan into Spool 10402, which is redistributed by hash
     code to all AMPs.
     13      We do an All-AMPs JOIN step from Spool 10400 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10401. Spool 10400 and
     Spool 10401 are joined using a merge join . The result goes into Spool
     10403, which is redistributed by hash code to all AMPs.
     14      We do an All-AMPs JOIN step from Spool 10402 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10403. Spool 10402 and
     Spool 10403 are joined using ahash join of 20 partitions. The result
     goes into Spool 10388, which is built locally on the AMPs.
     15      We do a SUM step to aggregate from Spool 10388 by way of an
     all-rows scan. Aggregate Intermediate Results are computed globally,
     then placed in Spool 10408.
     16      We do a SUM step to aggregate from Spool 10408 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10410.
     17      We do an All-AMPs RETRIEVE step from Spool 10410 (Last Use) by
     way of an all-rows scan into Spool 10405, which is built locally on the
     AMPs. This step begins a parallel block of steps.
     17      We do a SUM step to aggregate from Spool 10388 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10414. This step ends a parallel block of
     steps.
     18      We do a SUM step to aggregate from Spool 10414 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10416.
     19      We do an All-AMPs RETRIEVE step from Spool 10416 (Last Use) by
     way of an all-rows scan into Spool 10404, which is built locally on the
     AMPs.
     20      We do an All-AMPs RETRIEVE step from Spool 10404 (Last Use) by
     way of an all-rows scan into Spool 10418, which is redistributed by hash
     code to all AMPs. This step begins a parallel block of steps.
     20      We do an All-AMPs RETRIEVE step from Spool 10405 (Last Use) by
     way of an all-rows scan into Spool 10419, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     21      We do an All-AMPs JOIN step from Spool 10418 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10419. Spool 10418 and
     Spool 10419 are joined using a merge join . The result goes into Spool
     10386, which is built locally on the AMPs.
     22      We send out an END TRANSACTION step to all AMPs involved in
     processing the request.

     ***********************************************************************
     Explain of Blue Line 2
     1       First, lock dp_sb_ca."pseudo table" for read on a row hash.
     2       Next, we lock dp_sb_ca."pseudo table" for read on a row hash.
     3       We lock DP_FCMDM.DATA_SOURCES_FILTER for access, we lock
     dp_sb_ca.x_rate_hist for read, we lock dp_sb_ca.pdt_group for read, we
     lock DP_TCMDM.TM_ME_PARTY_ORGANISATION for access, we lock
     DP_TCMDM.TM_ME_PARTY_INDIVIDUAL for access, we lock
     DP_TCMDM.TM_ME_TERM_DEPOSIT for access, we lock
     DP_TEDW.T0521_FINANCIAL_EVENT for access, we lock
     DP_TEDW.T0504_ACCOUNT_EVENT for access, we lock DP_TEDW.T0500_EVENT for
     access, we lock DP_TEDW.T0300_AGREEMENT for access and we lock
     DP_TEDW.B0200_MAP for access.
     4       We do an All-AMPs RETRIEVE step from
     DP_TCMDM.TM_ME_PARTY_INDIVIDUAL by way of an all-rows scan into Spool
     10420, which is redistributed by hash code to all AMPs.
     5       We do an All-AMPs RETRIEVE step from
     DP_TCMDM.TM_ME_PARTY_ORGANISATION by way of an all-rows scan into Spool
     10420, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     5       We do an All-AMPs RETRIEVE step from DP_TCMDM.TM_ME_TERM_DEPOSIT
     by way of an all-rows scan into Spool 10424, which is redistributed by
     hash code to all AMPs. This step ends a parallel block of steps.
     6       We do an All-AMPs JOIN step from Spool 10424 (Last Use) by way
     of an all-rows scan, which is joined to table T0300_AGREEMENT. Spool
     10424 and table T0300_AGREEMENT are joined using a merge join . The
     result goes into Spool 10425, which is built locally on the AMPs. This
     step begins a parallel block of steps.
     6       We do an All-AMPs RETRIEVE step from
     DP_FCMDM.DATA_SOURCES_FILTER by way of an all-rows scan into Spool
     10427, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     7       We do an All-AMPs RETRIEVE step from Spool 10427 (Last Use) by
     way of an all-rows scan into Spool 10426, which is duplicated on all
     AMPs. This step begins a parallel block of steps.
     7       We do an All-AMPs RETRIEVE step from dp_sb_ca.pdt_group by way
     of an all-rows scan into Spool 10428, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     8       We do an All-AMPs JOIN step from Spool 10428 (Last Use) by way
     of an all-rows scan, which is joined to table B0200_MAP. Spool 10428 and
     table B0200_MAP are joined using a merge join . The result goes into
     Spool 10429, which is redistributed by hash code to all AMPs. This step
     begins a parallel block of steps.
     8       We do an All-AMPs JOIN step from Spool 10425 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10426. Spool 10425 and
     Spool 10426 are joined using an inclusion merge join . The result goes
     into Spool 10430, which is redistributed by hash code to all AMPs. This
     step is performed in parallel.
     8       We do an All-AMPs RETRIEVE step from DP_TEDW.T0500_EVENT by way
     of an all-rows scan into Spool 10431, which is built locally on the
     AMPs. This step is performed in parallel.
     8       We do an All-AMPs RETRIEVE step from dp_sb_ca.x_rate_hist by way
     of an all-rows scan into Spool 10432, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     9       We do an All-AMPs JOIN step from Spool 10431 (Last Use) by way
     of an all-rows scan, which is joined to table T0521_FINANCIAL_EVENT.
     Spool 10431 and table T0521_FINANCIAL_EVENT are joined using a merge
     join . The result goes into Spool 10433, which is redistributed by hash
     code to all AMPs.
     10      We do an All-AMPs JOIN step from Spool 10432 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10433. Spool 10432 and
     Spool 10433 are joined using a product join . The result goes into Spool
     10434, which is redistributed by hash code to all AMPs. This step begins
     a parallel block of steps.
     10      We do an All-AMPs JOIN step from Spool 10429 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10430. Spool 10429 and
     Spool 10430 are joined using a product join . The result goes into Spool
     10435, which is redistributed by hash code to all AMPs. This step ends a
     parallel block of steps.
     11      We do an All-AMPs JOIN step from Spool 10434 (Last Use) by way
     of an all-rows scan, which is joined to table T0504_ACCOUNT_EVENT. Spool
     10434 and table T0504_ACCOUNT_EVENT are joined using a merge join . The
     result goes into Spool 10436, which is redistributed by hash code to all
     AMPs.
     12      We do an All-AMPs RETRIEVE step from Spool 10420 (Last Use) by
     way of an all-rows scan into Spool 10437, which is redistributed by hash
     code to all AMPs.
     13      We do an All-AMPs JOIN step from Spool 10435 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10436. Spool 10435 and
     Spool 10436 are joined using a merge join . The result goes into Spool
     10438, which is redistributed by hash code to all AMPs.
     14      We do an All-AMPs JOIN step from Spool 10437 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10438. Spool 10437 and
     Spool 10438 are joined using ahash join of 20 partitions. The result
     goes into Spool 10423, which is built locally on the AMPs.
     15      We do a SUM step to aggregate from Spool 10423 by way of an
     all-rows scan. Aggregate Intermediate Results are computed globally,
     then placed in Spool 10443.
     16      We do a SUM step to aggregate from Spool 10443 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10445.
     17      We do an All-AMPs RETRIEVE step from Spool 10445 (Last Use) by
     way of an all-rows scan into Spool 10440, which is built locally on the
     AMPs. This step begins a parallel block of steps.
     17      We do a SUM step to aggregate from Spool 10423 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10449. This step ends a parallel block of
     steps.
     18      We do a SUM step to aggregate from Spool 10449 (Last Use) by way
     of an all-rows scan. Aggregate Intermediate Results are computed
     globally, then placed in Spool 10451.
     19      We do an All-AMPs RETRIEVE step from Spool 10451 (Last Use) by
     way of an all-rows scan into Spool 10439, which is built locally on the
     AMPs.
     20      We do an All-AMPs RETRIEVE step from Spool 10439 (Last Use) by
     way of an all-rows scan into Spool 10453, which is redistributed by hash
     code to all AMPs. This step begins a parallel block of steps.
     20      We do an All-AMPs RETRIEVE step from Spool 10440 (Last Use) by
     way of an all-rows scan into Spool 10454, which is redistributed by hash
     code to all AMPs. This step ends a parallel block of steps.
     21      We do an All-AMPs JOIN step from Spool 10453 (Last Use) by way
     of an all-rows scan, which is joined to Spool 10454. Spool 10453 and
     Spool 10454 are joined using a merge join . The result goes into Spool
     10421, which is built locally on the AMPs.
     22      We send out an END TRANSACTION step to all AMPs involved in
     processing the request.


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016