Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 23 Feb 2013 @ 01:12:52 GMT


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


Subj:   Re: Explain Plan-- Partitioned table
 
From:   Frydryszak, Marek

I believe it is a bug in the plan describer.

Pay attention that the plan is self-contradictory. Redistribution means changing hashing columns, but they are the same as in the source table.

I noticed, that since 13.10 plan are sometimes wrongly reported. I hope it is only problem in description....

E.g. let's consider below plan.

Product join in step 11.2 joins spools 23 and 24, but both are redistributed ( in steps 8.6 and 9.1 ), while product join needs one spool duplicated on all amps. On the other hand merge join in step 9.1 joins spools 19 and 20, but spool 19 is duplicated on all amps in step 8.2. Merge join doesn't require the same rows on which amp - does it ?

     8) We execute the following steps in parallel.
               ...
            2) We do an all-AMPs RETRIEVE step from
               DB50_Dane_Ref.T5002_kateg in view VD_US_FULL.kateg by way of
               an all-rows scan with no residual conditions into Spool 19
               (all_amps) (compressed columns allowed), which is duplicated
               on all AMPs.  Then we do a SORT to order Spool 19 by the hash
               ...
            6) We do an all-AMPs RETRIEVE step from
               ....
               into Spool 23 (all_amps) (compressed columns allowed), which
               is redistributed by the hash code of (
               DB08_Produkt.T0808_Prod_Grupa_Prod_Hist.id_prod_grupa) to all
               AMPs.  ....
     9) We execute the following steps in parallel.
            1) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way
               of a RowHash match scan, which is joined to Spool 20 (Last
               Use) by way of a RowHash match scan.  Spool 19 and Spool 20
               are right outer joined using a merge join, with condition(s)
               used for non-matching on right table ("(NOT (id_kateg IS NULL
               )) AND (NOT (id_typ_kateg IS NULL ))"), with a join condition
               of ("(id_kateg = ID_KATEG) AND (id_typ_kateg = ID_TYP_KATEG)").
               The result goes into Spool 24 (all_amps) (compressed columns
               allowed), which is redistributed by the hash code of (
               DB50_Dane_Ref.T5006_kateg_prod_grupa_rel.id_prod_grupa) to
               all AMPs.  The size of Spool 24 is estimated with low
               ...
     11) We execute the following steps in parallel.
              ...
           2) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of
              an all-rows scan, which is joined to Spool 24 (Last Use) by
              way of an all-rows scan.  Spool 23 and Spool 24 are left outer
              joined using a product join, with a join condition of (
              ...


     
  <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: 23 Jun 2019