Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Thu, 07 Apr 2005 @ 13:53:10 GMT


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


Subj:   Re: V2R5.1 dynamic partition elimination
 
From:   Wayne Schachtel

I was lead to believe in the software release below - dynamic partition elimination would work. It appears partitioned values in a predicate still need to be explicitly passed. Can anyone shed any light on this topic?

     InfoKey InfoData
     RELEASE V2R.05.01.02.03
     VERSION 05.01.02.05


     CREATE SET TABLE prdpos.pos_txn ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           txn_id INTEGER,
           str_nbr INTEGER,
           per_id INTEGER,
           register_nbr INTEGER,
           txn_type BYTEINT,
           txn_nbr INTEGER)
     PRIMARY INDEX ( txn_id )
     PARTITION BY RANGE_N(per_id  BETWEEN 1  AND 365  EACH 1 );

     CREATE SET TABLE prdpos.dim_time ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           per_id INTEGER,
           cal_date DATE FORMAT 'YYYY-MM-DD')
     UNIQUE PRIMARY INDEX ( per_id );


     EXAMPLES 1 - 3 :  DYNAMIC PARTITION ELIMINATION NOT WORKING


     example 1

     select a.cal_date, b.txn_id, b.str_nbr, b.register_nbr, b.txn_type, b.txn_nbr
       from dim_time a
        join pos_txn  b
          on  a.per_id = b.per_id
     where  a.cal_date between '2005-04-03' and '2005-04-04'  ;


      Explanation
        1) First, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.b.
        2) Next, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.a.
        3) We lock PRDPOS.b for read, and we lock PRDPOS.a for read.
        4) We do an all-AMPs RETRIEVE step from all partitions of PRDPOS.b
           with a condition of ("NOT (PRDPOS.b.per_id IS NULL)") into Spool 2
           (all_amps), 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 high confidence to be 28 rows.  The estimated time
           for this step is 0.01 seconds.
        5) We do an all-AMPs JOIN step from PRDPOS.a by way of a RowHash
           match scan with a condition of ("(PRDPOS.a.cal_date <= DATE
           '2005-04-04') AND (PRDPOS.a.cal_date >= DATE '2005-04-03')"),
           which is joined to Spool 2 (Last Use) by way of a RowHash match
           scan.  PRDPOS.a and Spool 2 are joined using a merge join, with a
           join condition of ("PRDPOS.a.per_id = per_id").  The result goes
           into Spool 1 (group_amps), which is built locally on the AMPs.
           The size of Spool 1 is estimated with no confidence to be 10 rows.
           The estimated time for this step is 0.03 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.04 seconds.


     example 2

     select a.cal_date, b.txn_id, b.str_nbr, b.register_nbr, b.txn_type, b.txn_nbr
       from dim_time a
        join pos_txn  b
          on  a.per_id = b.per_id
     where b.per_id in
     (select per_id from dim_time
       where cal_date between '2005-04-03' and '2005-04-04' )


       Explanation
        1) First, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.b.
        2) Next, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.ws_dim_time.
        3) We lock PRDPOS.b for read, and we lock PRDPOS.ws_dim_time for read.
        4) We execute the following steps in parallel.
             1) We do an all-AMPs RETRIEVE step from all partitions of
                PRDPOS.b with a condition of ("NOT (PRDPOS.b.per_id IS NULL)")
                into Spool 2 (all_amps), 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 high confidence to be 28 rows.  The
                estimated time for this step is 0.01 seconds.
             2) We do an all-AMPs RETRIEVE step from PRDPOS.ws_dim_time by
                way of an all-rows scan with a condition of (
                "(PRDPOS.ws_dim_time.cal_date <= DATE '2005-04-04') AND
                (PRDPOS.ws_dim_time.cal_date >= DATE '2005-04-03')") into
                Spool 3 (all_amps), 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 no confidence to be 28 rows.  The estimated
                time for this step is 0.01 seconds.
        5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
           all-rows scan, which is joined to Spool 3 (Last Use) by way of an
           all-rows scan.  Spool 2 and Spool 3 are joined using an inclusion
           merge join, with a join condition of ("per_id = per_id").  The
           result goes into Spool 4 (all_amps), which is redistributed by
           hash code to all AMPs.  Then we do a SORT to order Spool 4 by row
           hash.  The size of Spool 4 is estimated with index join confidence
           to be 10 rows.  The estimated time for this step is 0.01 seconds.
        6) We do an all-AMPs JOIN step from PRDPOS.a by way of a RowHash
           match scan with no residual conditions, which is joined to Spool 4
           (Last Use) by way of a RowHash match scan.  PRDPOS.a and Spool 4
           are joined using a merge join, with a join condition of (
           "PRDPOS.a.per_id = per_id").  The result goes into Spool 1
           (group_amps), which is built locally on the AMPs.  The size of
           Spool 1 is estimated with index join confidence to be 10 rows.
           The estimated time for this step is 0.03 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.04 seconds.


     example 3

     select a.cal_date, b.txn_id, b.str_nbr, b.register_nbr, b.txn_type, b.txn_nbr
       from dim_time a
       join pos_txn  b
        on a.per_id = b.per_id
      where b.per_id >= (select per_id from dim_time where cal_date = '2005-04-04')
          and b.per_id <= (select per_id from dim_time where cal_date = '2005-04-05');


      Explanation
        1) First, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.b.
        2) Next, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.ws_dim_time.
        3) We lock PRDPOS.b for read, and we lock PRDPOS.ws_dim_time for read.
        4) We do an all-AMPs RETRIEVE step from PRDPOS.ws_dim_time by way of
           an all-rows scan with a condition of (
           "PRDPOS.ws_dim_time.cal_date = DATE '2005-04-04'") into Spool 2
           (all_amps), which is built locally on the AMPs.  The size of Spool
           2 is estimated with no confidence to be 1 row.  The estimated time
           for this step is 0.03 seconds.
        5) We do an all-AMPs SUM step to aggregate from Spool 2 by way of an
           all-rows scan.  Aggregate Intermediate Results are computed
           globally, then placed in Spool 3.
        6) We execute the following steps in parallel.
             1) We do an all-AMPs ABORT test from Spool 3 (Last Use) by way
                of an all-rows scan with a condition of ("Field_2 > 1").
             2) We do an all-AMPs RETRIEVE step from PRDPOS.ws_dim_time by
                way of an all-rows scan with a condition of (
                "PRDPOS.ws_dim_time.cal_date = DATE '2005-04-05'") into Spool
                5 (all_amps), which is built locally on the AMPs.  The size
                of Spool 5 is estimated with no confidence to be 1 row.  The
                estimated time for this step is 0.03 seconds.
             7) We do an all-AMPs SUM step to aggregate from Spool 5 by way of an
                all-rows scan.  Aggregate Intermediate Results are computed
                globally, then placed in Spool 6.
        8) We execute the following steps in parallel.
             1) We do an all-AMPs ABORT test from Spool 6 (Last Use) by way
                of an all-rows scan with a condition of ("Field_2 > 1").
             2) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by
                way of an all-rows scan into Spool 8 (all_amps), which is
                duplicated on all AMPs.  The size of Spool 8 is estimated
                with high confidence to be 28 rows.  The estimated time for
                this step is 0.03 seconds.
        9) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an
           all-rows scan, which is joined to all partitions of PRDPOS.b with
           a condition of ("NOT (PRDPOS.b.per_id IS NULL)").  Spool 8 and
           PRDPOS.b are joined using a product join, with a join condition of
           ("PRDPOS.b.per_id <= per_id").  The result goes into Spool 9
           (all_amps), which is redistributed by hash code to all AMPs.  Then
           we do a SORT to order Spool 9 by row hash.  The size of Spool 9 is
           estimated with no confidence to be 9 rows.  The estimated time for
           this step is 0.02 seconds.
       10) We do an all-AMPs JOIN step from PRDPOS.a by way of a RowHash
           match scan with no residual conditions, which is joined to Spool 9
           (Last Use) by way of a RowHash match scan.  PRDPOS.a and Spool 9
           are joined using a merge join, with a join condition of (
           "PRDPOS.a.per_id = per_id").  The result goes into Spool 10
           (all_amps), which is duplicated on all AMPs.  The size of Spool 10
           is estimated with no confidence to be 280 rows.  The estimated
           time for this step is 0.03 seconds.
       11) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
           all-rows scan, which is joined to Spool 10 (Last Use) by way of an
           all-rows scan.  Spool 2 and Spool 10 are joined using a product
           join, with a join condition of ("per_id >= per_id").  The result
           goes into Spool 1 (group_amps), which is built locally on the AMPs.
           The size of Spool 1 is estimated with no confidence to be 3 rows.
           The estimated time for this step is 0.04 seconds.
       12) 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.


     EXAMPLE 4  - PARTITION ELIMINATION DOES WORK

     select a.cal_date, b.txn_id, b.str_nbr, b.register_nbr, b.txn_type, b.txn_nbr
       from dim_time a
        join pos_txn  b
          on  a.per_id = b.per_id
     where b.per_id between 1 and 3 ;

       Explanation
        1) First, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.b.
        2) Next, we lock a distinct PRDPOS."pseudo table" for read on a
           RowHash to prevent global deadlock for PRDPOS.a.
        3) We lock PRDPOS.b for read, and we lock PRDPOS.a for read.
        4) We do an all-AMPs RETRIEVE step from 3 partitions of PRDPOS.b with
           a condition of ("(NOT (PRDPOS.b.per_id IS NULL )) AND
           ((PRDPOS.b.per_id <= 3) AND (PRDPOS.b.per_id >= 1 ))") into Spool
           2 (all_amps), 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 high confidence to be 28 rows.  The estimated
           time for this step is 0.01 seconds.
        5) We do an all-AMPs JOIN step from PRDPOS.a by way of a RowHash
           match scan with a condition of ("(PRDPOS.a.per_id >= 1) AND
           (PRDPOS.a.per_id <= 3)"), which is joined to Spool 2 (Last Use) by
           way of a RowHash match scan.  PRDPOS.a and Spool 2 are joined
           using a merge join, with a join condition of ("PRDPOS.a.per_id =
           per_id").  The result goes into Spool 1 (group_amps), which is
           built locally on the AMPs.  The size of Spool 1 is estimated with
           low confidence to be 32 rows.  The estimated time for this step is
           0.03 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.04 seconds.

Wayne Schachtel
Wayne Schachtel Consulting



     
  <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: 28 Jun 2020