Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 07 Feb 2011 @ 17:44:44 GMT


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


Subj:   Dynamic Partition Elimination
 
From:   MARIA JOSEFA RODRIGUEZ POSADA

Hi,

For the last 2 weeks we are having problems with our loading and to be precisely with the execution of our programmed reports. They are lasting much more than usual, but the amount of data didn4t increase that much.

We think that we were able to detect the point of the consults where the problem is (it is way the slower):

We do an All-AMPs JOIN step from Spool 32237 (Last Use) by way of an all-rows scan, which is joined to table FAC_MOVIMIENTOS_ALMACEN. Spool 32237 and table FAC_MOVIMIENTOS_ALMACEN are joined using a product join enhanced by Dynamic Partition Elimination. The result goes into Spool 32239, which is built locally on the AMPs. This step is performed in parallel.

The explain for one of the consults would be:

      1  0:00,00    0:00,05          0         1   First, lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      2  0:00,00    0:00,06          0         1   Next, we lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      3  0:00,00    0:00,06          0         1   We lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      4  0:00,00    0:00,03          0         1   We lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      5  0:00,00    0:04,85          0         1   We lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      6  0:00,00    0:00,07          0         1   We lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      7  0:00,00    0:00,06          0         1   We lock DIM_VSE_PROD."pseudo table"
                                                   for read on a row hash.

      8  0:00,00    0:04,20          0        40   We lock DIM_VSE_PROD.DIM_MODELO_COLOR
                                                   for read, we lock DIM_VSE_PROD.DIM_MODELO
                                                   for read, we lock DIM_VSE_PROD.DIM_TIENDA
                                                   for read, we lock DIM_VSE_PROD.DIM_TEMPORADA
                                                   for read, we lock DIM_VSE_PROD.DIM_DIA for read,
                                                   we lock DIM_VSE_PROD.FAC_MOVIMIENTOS_ALMACEN
                                                   for read and we lock DIM_VSE_PROD.DIM_GENERO
                                                   for read.

      9  0:00,01    0:12,96        280      2400   We do an All-AMPs RETRIEVE step from
                                                   DIM_VSE_PROD.DIM_TEMPORADA by way of an all-rows
                                                   scan into Spool 32232, which is duplicated on
                                                   all AMPs. This step begins a parallel block
                                                   of steps.

      9  0:00,27    0:19,84      28604    286037   We do an All-AMPs RETRIEVE step from
                                                   DIM_VSE_PROD.DIM_MODELO_COLOR by way of an
                                                   all-rows scan into Spool 32233, which is
                                                   built locally on the AMPs. This step ends
                                                   a parallel block of steps.

     10  0:00,35   11:31,70      25640   5413120   We do an All-AMPs JOIN step from Spool 32232
                                                   (Last Use) by way of an all-rows scan, which
                                                   is joined to table DIM_MODELO. Spool 32232 and
                                                   table DIM_MODELO are joined using asingle
                                                   partition hash join. The result goes into
                                                   Spool 32234, which is duplicated on all AMPs.

     11  0:00,01    0:32,52        160       160   We do an All-AMPs RETRIEVE step from
                                                   DIM_VSE_PROD.DIM_GENERO by way of an all-rows
                                                   scan into Spool 32235, which is duplicated on
                                                   all AMPs. This step begins a parallel block
                                                   of steps.

     11  0:00,02    0:27,31        267    284910   We do an All-AMPs JOIN step from Spool 32233
                                                   (Last Use) by way of an all-rows scan, which is
                                                   joined to Spool 32234.  Spool 32233 and Spool 32234
                                                   are joined using a merge join .  The result goes
                                                   into Spool 32236, which is built locally on the AMPs.
                                                   This step is performed in parallel.

     11  0:00,01    0:35,76         40        40   We do an All-AMPs RETRIEVE step from
                                                   DIM_VSE_PROD.DIM_TEMPORADA by way of an all-rows scan
                                                   into Spool 32237, which is duplicated on all AMPs.
                                                   This step ends a parallel block of steps.

     12  0:00,03   24:50,36      10680  11396400   We do an All-AMPs JOIN step from Spool 32235 (Last Use)
                                                   by way of an all-rows scan, which is      joined to
                                                   Spool 32236. Spool 32235 and Spool 32236 are joined
                                                   using a merge join . The result goes into Spool 32238,
                                                   which is duplicated on all AMPs. This step begins a
                                                   parallel block of steps.

     12  0:04,89    0:53,22   12495470  66195750   We do an All-AMPs JOIN step from Spool 32237 (Last Use)
                                                   by way of an all-rows scan, which is joined to table
                                                   FAC_MOVIMIENTOS_ALMACEN. Spool 32237 and table
                                                   FAC_MOVIMIENTOS_ALMACEN are joined using a product join
                                                   enhanced by Dynamic Partition Elimination. The result
                                                   goes into Spool 32239, which is built locally on the AMPs.
                                                   This step is performed in parallel.

     12  0:00,01    0:30,24      16080     16080   We do an All-AMPs RETRIEVE step from DIM_VSE_PROD.DIM_DIA
                                                   by way of an all-rows scan into Spool 32240, which is
                                                   duplicated on all AMPs. This step ends a parallel block
                                                   of steps.

     13  0:00,87   10:25,32      17323  64347890   We do an All-AMPs JOIN step from Spool 32238 (Last Use)
                                                   by way of an all-rows scan, which is joined to
                                                   Spool 32239. Spool 32238 and Spool 32239 are joined
                                                   using asingle partition hash join . The result goes into
                                                   Spool 32241, which is built locally on the AMPs.

     14  0:00,04   1890                            We do an All-AMPs JOIN step from Spool 32240 (Last Use)
                                                   by way of an all-rows scan, which is joined to Spool 32241.
                                                   Spool 32240 and Spool 32241 are joined using a merge join .
                                                   The result goes into Spool 32242, which is redistributed
                                                   by hash code to all AMPs.

     15  0:00,03   1890                            We do an All-AMPs JOIN step from DIM_VSE_PROD.DIM_TIENDA
                                                   by way of an all-rows scan, which is joined to Spool 32242.
                                                   table DIM_TIENDA and Spool 32242 are joined using a
                                                   merge join . The result goes into Spool 32231, which is
                                                   built locally on the AMPs.

     16  0:00,06   1418                            We do a SUM step to aggregate from Spool 32231 (Last Use)
                                                   by way of an all-rows scan. Aggregate Intermediate Results
                                                   are computed globally, then placed in Spool 32243.

     17  0:00,02   1418                            We do an All-AMPs RETRIEVE step from Spool 32243 (Last Use)
                                                   by way of an all-rows scan into Spool 32229, which is built
                                                   locally on the AMPs.

The fact table:

     CREATE MULTISET TABLE DIM_VSE_PROD.FAC_MOVIMIENTOS_ALMACEN ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           id_temporada_movimiento SMALLINT,
           id_destino INTEGER,
           id_origen INTEGER COMPRESS (0 ,15216 ,11907 ,13522 ,-2 ),
           id_item INTEGER,
           id_modelo_color INTEGER,
           id_modelo INTEGER,
           id_divisa INTEGER COMPRESS (9 ,27 ,32 ,34 ,37 ,60 ,63 ,80 ,82 ,87 ),
           id_tipo_movimiento_dwh SMALLINT COMPRESS (1 ,3 ,4 ,5 ,7 ,9 ,13 ,14 ,16 ,91 ),
           fec_movimiento DATE FORMAT 'YY/MM/DD',
           imp_por_impuesto DECIMAL(10,4),
           num_traspaso VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
           num_ampliacion VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
           val_acum INTEGER COMPRESS (1 ,2 ,3 ,4 ,6 ,-6 ,-4 ,-3 ,-2 ,-1 ),
           imp_pvp_tienda_eur DECIMAL(15,3),
           imp_pvp_tienda_div DECIMAL(15,3),
           valor_factura DECIMAL(15,3),
           imp_precio_fact_dev_eur DECIMAL(15,3),
           imp_precio_fact_dev_div DECIMAL(15,3),
           fec_modificacion DATE FORMAT 'YY/MM/DD') PRIMARY INDEX ( id_destino ,id_item )
     PARTITION BY id_temporada_movimiento + 1 INDEX idxMA1 ( id_destino ) INDEX idxMA2 (id_item );

We would appreciate any help in this subject.


Thanks

Fina Rodrmguez Posada
Dpto Sistemas de informacisn
Area de SCM y Business Intelligence



     
  <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: 15 Jun 2023