Archives of the TeradataForum

Message Posted: Mon, 07 Feb 2011 @ 17:46:51 GMT


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

Subj:		Different accesses: same table, same query

From:		MARIA JOSEFA RODRIGUEZ POSADA

Hi,

We have two databases: PROD and DESA. Tables are the same and DESA keeps much less data.

We execute the next query in both environments:

     SELECT COUNT(*)

        FROM FAC_MOVIMIENTOS_ALMACEN A
             INNER JOIN DIM_TEMPORADA B
             ON (A.id_temporada_movimiento = B.id_temporada)

     WHERE id_temporada_movimiento = 61;

Tables are defined as follow:

     CREATE MULTISET TABLE 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 );


     CREATE SET TABLE DIM_TEMPORADA ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           id_temporada SMALLINT,
           des_temporada VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
           cod_tipo_temporada CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
           des_tipo_temporada VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC,
           anyo_temporada VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
           num_temporada CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           des_num_temporada VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
           orden_temporada VARCHAR(9) CHARACTER SET UNICODE NOT CASESPECIFIC,
           temporada_corta VARCHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,
           ind_activa CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           ind_actual CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           ind_inventario_visible CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           ind_actual_aa VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
           cod_temporada CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( id_temporada );

-How can it be that the explain for the same query is different??? ?It seems like one access is by index and the other by partition? ?Why?.

-Why in the PROD environment I get this "are joined using a product join" and not in DESA??

I think that there is something with this table FAC_MOVIMIENTOS_ALMACEN and this is the cause for our lately delayed in loading and reports executions.

Please, Anyone has any idea?

Thanks very much

Explain PROD ENVIRONMENT:

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct DIM_VSE_PROD."pseudo table" for read on a RowHash to prevent global deadlock for DIM_VSE_PROD.A.`
	`2)`	`Next, we lock DIM_VSE_PROD.A for read.`
	`3)`	`We do a single-AMP RETRIEVE step from DIM_VSE_PROD.B by way of the unique primary index "DIM_VSE_PROD.B.id_temporada = 61" with no residual conditions into Spool 4 (all_amps), which is duplicated on all AMPs. The size of Spool 4 is estimated with high confidence to be 40 rows (600 bytes). The estimated time for this step is 0.01 seconds.`
	`4)`	We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to a single partition of DIM_VSE_PROD.A with a condition of ("DIM_VSE_PROD.A.id_temporada_movimiento = 61") with a residual condition of ("DIM_VSE_PROD.A.id_temporada_movimiento = 61"). Spool 4 and DIM_VSE_PROD.A are joined using a product join, with a join condition of ("DIM_VSE_PROD.A.id_temporada_movimiento = id_temporada"). The input table DIM_VSE_PROD.A will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The size of Spool 3 is estimated with high confidence to be 62,982,945 rows (944,744,175 bytes). The estimated time for this step is 6.12 seconds.
	`5)`	`We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 5. The size of Spool 5 is estimated with high confidence to be 1 row (23 bytes). The estimated time for this step is 5.00 seconds.`
	`6)`	`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 size of Spool 1 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.00 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 11.13 seconds.`

Explain DESA Environment:

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct DIM_VSE_DESA."pseudo table" for read on a RowHash to prevent global deadlock for DIM_VSE_DESA.A.`
	`2)`	`Next, we lock DIM_VSE_DESA.A for read.`
	`3)`	We do an all-AMPs RETRIEVE step from a single partition of DIM_VSE_DESA.A with a condition of ("DIM_VSE_DESA.A.id_temporada_movimiento = 61") with a residual condition of ("(DIM_VSE_DESA.A.id_temporada_movimiento = 61) AND (NOT (DIM_VSE_DESA.A.id_temporada_movimiento IS NULL ))") into Spool 4 (one-amp), which is redistributed by the hash code of (61) to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with high confidence to be 1 row (16 bytes). The estimated time for this step is 0.01 seconds.
	`4)`	We do a single-AMP JOIN step from DIM_VSE_DESA.B by way of the unique primary index "DIM_VSE_DESA.B.id_temporada = 61" with no residual conditions, which is joined to Spool 4 (Last Use) by way of a RowHash match scan. DIM_VSE_DESA.B and Spool 4 are joined using a merge join, with a join condition of ("id_temporada_movimiento = DIM_VSE_DESA.B.id_temporada"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The size of Spool 3 is estimated with high confidence to be 1 row (15 bytes). The estimated time for this step is 0.00 seconds.
	`5)`	`We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 5. The size of Spool 5 is estimated with high confidence to be 1 row (23 bytes). The estimated time for this step is 0.02 seconds.`
	`6)`	`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 size of Spool 1 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.00 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.03 seconds.`


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

Attachments

Library

Quick Reference