Archives of the TeradataForum

Message Posted: Thu, 06 Jul 2006 @ 21:09:07 GMT

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

Hi, i'm getting a problem with this query:

     select
        pc.cd_party,
        fac.fc_emision,
        pc.cd_producto_comercial,
        fac.cd_factura
     from
        tmp_segmentacion_ravi.tmp_productos_considerados_1 as pc
        inner join
        tmp_segmentacion_ravi.tmp_party_factura as fac
        on fac.cd_party =  pc.cd_party
     where
        fac.fc_emision between pc.fc_inicio_party_usuario and
     pc.fc_fin_party_usuario
     group by
        pc.cd_party,
        fac.fc_emision,
        pc.cd_producto_comercial,
        fac.cd_factura;
     tmp_segmentacion_ravi.tmp_productos_considerados_1: Have cd_party,
     cd_producto_instancia as UNIQUE PI
     tmp_segmentacion_ravi.tmp_party_factura: Have cd_party as PI (not unique)

This is the explain:

       1) First, we lock a distinct tmp_sfe."pseudo table" for read on a
          RowHash to prevent global deadlock for
          tmp_sfe.seg_ravi_party_factura.
       2) Next, we lock a distinct tmp_sfe."pseudo table" for read on a
          RowHash to prevent global deadlock for
          tmp_sfe.productos_considerados_1.
       3) We lock tmp_sfe.seg_ravi_party_factura for read, and we lock
          tmp_sfe.productos_considerados_1 for read.
       4) We do an all-AMPs RETRIEVE step from
          tmp_sfe.productos_considerados_1 by way of an all-rows scan with a
          condition of ("NOT (tmp_sfe.productos_considerados_1.cd_party IS
          NULL)") 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 no confidence to be 470,654
          rows.  The estimated time for this step is 2.56 seconds.
       5) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
          RowHash match scan, which is joined to
          tmp_sfe.seg_ravi_party_factura by way of a RowHash match scan.
          Spool 4 and tmp_sfe.seg_ravi_party_factura are joined using a
          merge join, with a join condition of (
          "(tmp_sfe.seg_ravi_party_factura.fc_emision >=
          fc_inicio_party_usuario) AND
          ((tmp_sfe.seg_ravi_party_factura.fc_emision <=
          fc_fin_party_usuario) AND (tmp_sfe.seg_ravi_party_factura.cd_party
          = cd_party ))").  The result goes into Spool 3 (all_amps), which
          is built locally on the AMPs.  The result spool file will not be
          cached in memory.  The size of Spool 3 is estimated with no
          confidence to be 11,860,496 rows.  The estimated time for this
          step is 18.44 seconds.
       6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
          way of an all-rows scan, and the grouping identifier in field 1.
          Aggregate Intermediate Results are computed locally, then placed
          in Spool 5.  The aggregate spool file will not be cached in memory.
          The size of Spool 5 is estimated with no confidence to be
          10,674,447 rows.  The estimated time for this step is 1 minute and
          30 seconds.
       7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
          an all-rows scan into Spool 1 (group_amps), which is built locally
          on the AMPs.  The result spool file will not be cached in memory.
          The size of Spool 1 is estimated with no confidence to be
          10,674,447 rows.  The estimated time for this step is 20.43
          seconds.
       8) 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 2 minutes and 11 seconds.

Both tables have around 550.000 records.

The spool space for the user is 50 GB. Any suggestion??

Thanks !!!!

Castelli, Emanuel Alejandro
Desarrollo DataWarehouse - IBM Argentina

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