Aggregated Join Index on 2 systems

Database
Teradata Employee

Aggregated Join Index on 2 systems

I have an aggregated join idex defined by joining a big table (fact) with some small ones (dim).

Queries running on system 1 (dev) make use of this index. But not on system 2 (prd). Statistics on both systems are the same. 

What could be the reasons why the optimizer didn't use the index on system 2? Thanks.

6 REPLIES

Re: Aggregated Join Index on 2 systems

With this much info, it is hard to say. It can be due to many reasons,Data  demography,freshness of stats, though they are same etc. Maybe you can share the explain of queries, masking important infos.

Cheers, 

Re: Aggregated Join Index on 2 systems

Hyma,

Optmizer makes its decisions based on many factors; it considers system configurations, stats, sql code, data demographics etc. So on one machine it might consider JI as a faster approach, and on the other hand, on the Prod machine there might be a different system configuration that lead to some other execution plan better than JI. 

Can you please share the explain plan?

Khurram
Teradata Employee

Re: Aggregated Join Index on 2 systems

Same Config on 2 systems: TD 14.00; 46AMPs; DDLs on 2 system identical; STATs freshly collected.  

Explain with Aggregate Join Index being used:

1) First, we lock DB_CONTROL.secrole in view

     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we

     lock DB_CONTROL.secrolemap in view

     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we

     lock DB_DWH.AJI_01 in view

     DB_VIEWDWH.FAK_BIG for access, we lock

     DB_DWH.DIM_STD in view

     DB_VIEWDWH.DIM_STD for access, we lock

     DB_DWH.FAK_BIG in view DB_VIEWDWH.FAK_BIG

     for access, we lock DB_DWH.DIM_SPUR in view

     DB_VIEWDWH.DIM_SPUR for access, we lock

     DB_DWH.DIM_MESS in view

     DB_VIEWDWH.DIM_MESS for access, we lock

     DB_DWH.DIM_DAT_MAND in view

     DB_VIEWDWH.DIM_DAT_MAND for access, and we lock

     DB_DWH.MV_DIM_FZGKL_PIVOT in view

     DB_VIEWDWH.MV_DIM_FZGKL_PI for access.

  2) Next, we execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view

          DB_VIEWDWH.MV_DIM_FZGKL_PI by way of an

          all-rows scan 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 2,806 rows (

          145,912 bytes).  The estimated time for this step is 0.01

          seconds.

       2) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view

          DB_VIEWDWH.DIM_DAT_MAND by way of a

          traversal of index # 4 without accessing the base table with

          a residual condition of ("(DB_DWH.dt in view

          DB_VIEWDWH.DIM_DAT_MAND.YEAR_NR = 2012) AND

          (DB_DWH.dt in view

          DB_VIEWDWH.DIM_DAT_MAND.MAND_BEZ = ‘ZZZ’)")

          into Spool 5 (all_amps), which is duplicated on all AMPs.

          The size of Spool 5 is estimated with no confidence to be

          32,062 rows (1,122,170 bytes).  The estimated time for this

          step is 0.01 seconds.

  3) We do an all-AMPs JOIN step from DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS by way of an all-rows scan

     with a condition of ("(NOT (DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.KT_BEZ IS NULL )) AND

     ((DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MAND_BEZ = ‘ZZZ’) AND

     ((DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE

     '%Volumen%') OR ((DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE

     '%Island%') OR (DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MESS_NR_BEZ =

     '158'))))"), which is joined to Spool 4 (Last Use) by way of an

     all-rows scanDB_DWH.dt and Spool 4 are joined using a

     product join, with a join condition of (

     "((DB_DWH.dt.MESS_NR_BEZ = '158') OR ((NOT

     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')) OR

     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))

     AND ((DB_DWH.dt.MESS_NR_BEZ = '158') OR

     (((DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Island%') OR (NOT

     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))

     AND (((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')

     OR (DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Volumen%'))))").

     The result goes into Spool 6 (all_amps), which is duplicated on

     all AMPs.  The size of Spool 6 is estimated with no confidence to

     be 20,654 rows (6,733,204 bytes).  The estimated time for this

     step is 0.02 seconds.

  4) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an

     all-rows scan, which is joined to a single partition of

     DB_DWH.AJI_01 in view

     DB_VIEWDWH.FAK_BIG with a condition of (

     "DB_DWH.AJI_01 in view

     DB_VIEWDWH.FAK_BIG.MESSTYP_ID  = 1").  Spool 5

     and DB_DWH.AJI_01 are joined using a dynamic hash

     join, with a join condition of (

     "(DB_DWH.AJI_01.MAND_BEZ = MAND_BEZ) AND

     (DB_DWH.AJI_01.DIM_DAT_MAND = DIMENSION_KEY)").

     The input table DB_DWH.AJI_01 will not be cached in

     memory.  The result goes into Spool 7 (all_amps), which is built

     locally on the AMPs.  The size of Spool 7 is estimated with no

     confidence to be 24,029,188 rows (1,658,013,972 bytes).  The

     estimated time for this step is 34.72 seconds.

  5) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view

     DB_VIEWDWH.DIM_SPUR by way of a traversal of

     index # 4 without accessing the base table with a residual

     condition of ("(DB_DWH.dt in view

     DB_VIEWDWH.DIM_SPUR.STAT_NR = 4) OR

     (DB_DWH.dt in view

     DB_VIEWDWH.DIM_SPUR.STAT_NR = 3)") into

     Spool 8 (all_amps), which is duplicated on all AMPs.  The size of

     Spool 8 is estimated with no confidence to be 874 rows (14,858

     bytes).  The estimated time for this step is 0.01 seconds.

  6) We do an all-AMPs JOIN step from DB_DWH.dt in view

     DB_VIEWDWH.DIM_STD by way of an all-rows scan with

     no residual conditions, which is joined to Spool 8 (Last Use) by

     way of an all-rows scanDB_DWH.dt and Spool 8 are joined

     using a product join, with a join condition of ("(1=1)").  The

     result goes into Spool 9 (all_amps), which is duplicated on all

     AMPs.  The size of Spool 9 is estimated with no confidence to be

     40,204 rows (1,969,996 bytes).  The estimated time for this step

     is 0.01 seconds.

  7) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an

     all-rows scan, which is joined to Spool 7 (Last Use) by way of an

     all-rows scanSpool 6 and Spool 7 are joined using a single

     partition hash join, with a join condition of ("(DIMENSION_KEY =

     DIM_FZG) AND ((MAND_BEZ = MAND_BEZ) AND

     ((DIM_MESS = DIMENSION_KEY) AND (MAND_BEZ = MAND_BEZ

     )))").  The result goes into Spool 10 (all_amps), which is built

     locally on the AMPs.  The size of Spool 10 is estimated with no

     confidence to be 1,981,471 rows (685,588,966 bytes).  The

     estimated time for this step is 1.39 seconds.

  8) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an

     all-rows scan, which is joined to Spool 10 (Last Use) by way of an

     all-rows scanSpool 9 and Spool 10 are joined using a single

     partition hash join, with a join condition of ("(DIM_SPUR =

     DIMENSION_KEY) AND (DIM_STD = DIMENSION_KEY)").  The result

     goes into Spool 3 (all_amps), which is built locally on the AMPs.

     The size of Spool 3 is estimated with no confidence to be

     3,065,143 rows (1,134,102,910 bytes).  The estimated time for this

     step is 1.45 seconds.

  9) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by

     way of an all-rows scan , grouping by field1 (

     DB_DWH.dt.MAND_BEZ ,( CASE WHEN

     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN

     ('boimg://Logi_ZZZ.gif') ELSE

     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')

     END) ,DB_DWH.dt.MESS_NR_BEZ

     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ

     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ

     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR

     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR

     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1

     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -

     ')||DB_DWH.dt.FKL_DE_BEZ

     ,DB_DWH.dt.MESS_TYP_NR

     ,DB_DWH.dt.MESS_TYP_DE_BEZ

     ,DB_DWH.AJI_01.DIM_DAT_MAND).  Aggregate

     Intermediate Results are computed globally, then placed in Spool

     12.  The size of Spool 12 is estimated with no confidence to be

     2,298,858 rows (3,388,516,692 bytes).  The estimated time for this

     step is 1.30 seconds.

 10) We do an all-AMPs SUM step to aggregate from Spool 12 (Last Use)

     by way of an all-rows scan , grouping by field1 (

     DB_DWH.dt.MAND_BEZ ,( CASE WHEN

     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN

     ('boimg://Logi_ZZZ.gif') ELSE

     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')

     END) ,DB_DWH.dt.MESS_NR_BEZ

     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ

     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ

     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR

     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR

     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1

     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -

     ')||DB_DWH.dt.FKL_DE_BEZ

     ,DB_DWH.dt.MESS_TYP_NR

     ,DB_DWH.dt.MESS_TYP_DE_BEZ).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 14.  The size

     of Spool 14 is estimated with no confidence to be 1,724,144 rows (

     2,527,595,104 bytes).  The estimated time for this step is 1.61

     seconds.

 11) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of

     an all-rows scan into Spool 1 (group_amps), which is redistributed

     by the hash code of (DB_DWH.dt.MESS_TYP_DE_BEZ,

     DB_DWH.dt.MESS_TYP_NR,

     (DB_DWH.dt.FKL_HIER_LVL  ||' -

     ')||DB_DWH.dt.FKL_DE_BEZ,

     COUNT(DB_DWH.AJI_01.DIM_DAT_MAND )(INTEGER),

     SUM(DB_DWH.AJI_01.ANZ_FZG )(BIGINT),

     DB_DWH.dt.STD_NR - 1, DB_DWH.dt.STD_BEZ,

     DB_DWH.dt.YEAR_NR, DB_DWH.dt.Y_COR_NR,

     DB_DWH.dt.X_COR_NR, DB_DWH.dt.RICT_2_BEZ,

     DB_DWH.dt.RICT_1_BEZ, DB_DWH.dt.KT_BEZ,

     DB_DWH.dt.STR_BEZ, DB_DWH.dt.MESS_BEZ,

     DB_DWH.dt.MESS_NR_BEZ, (CASE WHEN

     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN

     ('boimg://Logi_ZZZ.gif') ELSE

     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')

     END), DB_DWH.dt.MAND_BEZ) to all AMPs.  Then we do a

     SORT to order Spool 1 by the sort key in spool field1 eliminating

     duplicate rows.  The size of Spool 1 is estimated with no

     confidence to be 1,724,144 rows (2,218,973,328 bytes).  The

     estimated time for this step is 7.57 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.  The total estimated time is 48.08 seconds.

Explain with Aggregate Join Index NOT being used:

This query is optimized using type 2 profile T2_Linux64, profileid 21.

  1) First, we lock DB_CONTROL.secrole in view

     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we

     lock DB_CONTROL.secrolemap in view

     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we

     lock DB_DWH.MV_DIM_FZGKL_PIVOT in view

     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we

     lock DB_DWH.FAK_BIG in view

     DB_VIEWDWH.FAK_BIG for access, we lock

     DB_DWH.DIM_STD in view

     DB_VIEWDWH.DIM_STD for access, we lock

     DB_DWH.DIM_SPUR in view

     DB_VIEWDWH.DIM_SPUR for access, we lock

     DB_DWH.DIM_MESS in view

     DB_VIEWDWH.DIM_MESS for access, and we lock

     DB_DWH.DIM_DAT_MAND  in view

     DB_VIEWDWH.DIM_DAT_MAND  for access.

  2) Next, we execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view

          DB_VIEWDWH.MV_DIM_FZGKL_PI by way of an

          all-rows scan with no residual conditions into Spool 18

          (all_amps), which is duplicated on all AMPs.  The size of

          Spool 18 is estimated with high confidence to be 2,806 rows (

          145,912 bytes).  The estimated time for this step is 0.01

          seconds.

       2) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view

          DB_VIEWDWH.DIM_DAT_MAND  by way of a

          traversal of index # 4 without accessing the base table with

          a residual condition of ("(DB_DWH.dt in view

          DB_VIEWDWH.DIM_DAT_MAND .YEAR_NR = 2012) AND

          (DB_DWH.dt in view

          DB_VIEWDWH.DIM_DAT_MAND .MAND_BEZ = ‘ZZZ’)")

          into Spool 19 (all_amps), which is duplicated on all AMPs.

          Then we do a SORT to partition by rowkey.  The size of Spool

          19 is estimated with low confidence to be 16,836 rows (

          589,260 bytes).  The estimated time for this step is 0.01

          seconds.

       3) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view

          DB_VIEWDWH.DIM_STD by way of an all-rows scan

          with no residual conditions into Spool 20 (all_amps), which

          is duplicated on all AMPs.  The size of Spool 20 is estimated

          with high confidence to be 1,150 rows (51,750 bytes).  The

          estimated time for this step is 0.01 seconds.

  3) We do an all-AMPs JOIN step from DB_DWH.dt in view

     DB_VIEWDWH.DIM_SPUR by way of a traversal of

     index # 4 without accessing the base table with a residual

     condition of ("(DB_DWH.dt in view

     DB_VIEWDWH.DIM_SPUR.STAT_NR = 4) OR

     (DB_DWH.dt in view

     DB_VIEWDWH.DIM_SPUR.STAT_NR = 3)"), which is

     joined to Spool 20 (Last Use) by way of an all-rows scan.

     DB_DWH.dt and Spool 20 are joined using a product join,

     with a join condition of ("(1=1)").  The result goes into Spool 21

     (all_amps), which is duplicated on all AMPs.  The size of Spool 21

     is estimated with high confidence to be 69,000 rows (3,381,000

     bytes).  The estimated time for this step is 0.01 seconds.

  4) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an

     all-rows scan, which is joined to DB_DWH.dt in view

     DB_VIEWDWH.FAK_BIG with a condition of (

     "(DB_DWH.dt in view

     DB_VIEWDWH.FAK_BIG.MESSTYP_ID  = 1) AND

     (DB_DWH.dt in view

     DB_VIEWDWH.FAK_BIG.MAND_BEZ = ‘ZZZ’)").  Spool 19

     and DB_DWH.dt are joined using a dynamic hash join, with a

     join condition of ("(DB_DWH.dt.DIM_DAT_MAND  =

     DIMENSION_KEY) AND (DB_DWH.dt.MAND_BEZ = MAND_BEZ)")

     enhanced by dynamic partition elimination.  The input table

     DB_DWH.dt will not be cached in memory.  The result goes

     into Spool 22 (all_amps), which is built locally on the AMPs.  The

     size of Spool 22 is estimated with low confidence to be 12,286,408

     rows (946,053,416 bytes).  The estimated time for this step is

     3.24 seconds.

  5) We do an all-AMPs JOIN step from DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS by way of an all-rows scan

     with a condition of ("(NOT (DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.KT_BEZ IS NULL )) AND

     ((DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MAND_BEZ = ‘ZZZ’) AND

     ((DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE

     '%Volumen%') OR ((DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE

     '%Island%') OR (DB_DWH.dt in view

     DB_VIEWDWH.DIM_MESS.MESS_NR_BEZ =

     '158'))))"), which is joined to Spool 18 (Last Use) by way of an

     all-rows scanDB_DWH.dt and Spool 18 are joined using a

     product join, with a join condition of (

     "((DB_DWH.dt.MESS_NR_BEZ = '158') OR ((NOT

     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')) OR

     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))

     AND ((DB_DWH.dt.MESS_NR_BEZ = '158') OR

     (((DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Island%') OR (NOT

     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))

     AND (((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')

     OR (DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Volumen%'))))").

     The result goes into Spool 23 (all_amps), which is duplicated on

     all AMPs into 3 hash join partitions.  The size of Spool 23 is

     estimated with no confidence to be 184,322 rows (60,088,972 bytes).

     The estimated time for this step is 0.06 seconds.

  6) We do an all-AMPs JOIN step from Spool 21 (Last Use) by way of an

     all-rows scan, which is joined to Spool 22 (Last Use) by way of an

     all-rows scanSpool 21 and Spool 22 are joined using a single

     partition hash join, with a join condition of ("(DIM_STD =

     DIMENSION_KEY) AND (DIM_SPUR = DIMENSION_KEY)").  The

     result goes into Spool 24 (all_amps), which is built locally on

     the AMPs into 3 hash join partitions.  The size of Spool 24 is

     estimated with low confidence to be 19,117,855 rows (

     1,892,667,645 bytes).  The estimated time for this step is 2.95

     seconds.

  7) 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 scanSpool 23 and Spool 24 are joined using a hash join

     of 3 partitions, with a join condition of ("(DIMENSION_KEY =

     DIM_FZG) AND ((DIM_MESS = DIMENSION_KEY) AND

     ((MAND_BEZ = MAND_BEZ) AND (MAND_BEZ = MAND_BEZ )))").

     The result goes into Spool 17 (all_amps), which is built locally

     on the AMPs.  The size of Spool 17 is estimated with no confidence

     to be 14,125,991 rows (5,339,624,598 bytes).  The estimated time

     for this step is 5.85 seconds.

  8) We do an all-AMPs SUM step to aggregate from Spool 17 (Last Use)

     by way of an all-rows scan , grouping by field1 (

     DB_DWH.dt.MAND_BEZ ,( CASE WHEN

     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN

     ('boimg://Logi_ZZZ.gif') ELSE

     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')

     END) ,DB_DWH.dt.MESS_NR_BEZ

     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ

     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ

     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR

     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR

     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1

     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -

     ')||DB_DWH.dt.FKL_DE_BEZ

     ,DB_DWH.dt.MESS_TYP_NR

     ,DB_DWH.dt.MESS_TYP_DE_BEZ

     ,DB_DWH.dt.DIM_DAT_MAND ).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 26.  The

     aggregate spool file will not be cached in memory.  The size of

     Spool 26 is estimated with no confidence to be 10,594,494 rows (

     15,616,284,156 bytes).  The estimated time for this step is 5.51

     seconds.

  9) We do an all-AMPs SUM step to aggregate from Spool 26 (Last Use)

     by way of an all-rows scan , grouping by field1 (

     DB_DWH.dt.MAND_BEZ ,( CASE WHEN

     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN

     ('boimg://Logi_ZZZ.gif') ELSE

     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')

     END) ,DB_DWH.dt.MESS_NR_BEZ

     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ

     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ

     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR

     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR

     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1

     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -

     ')||DB_DWH.dt.FKL_DE_BEZ

     ,DB_DWH.dt.MESS_TYP_NR

     ,DB_DWH.dt.MESS_TYP_DE_BEZ).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 28.  The

     aggregate spool file will not be cached in memory.  The size of

     Spool 28 is estimated with no confidence to be 7,945,871 rows (

     11,648,646,886 bytes).  The estimated time for this step is 8.39

     seconds.

 10) We do an all-AMPs RETRIEVE step from Spool 28 (Last Use) by way of

     an all-rows scan into Spool 1 (group_amps), which is redistributed

     by the hash code of (DB_DWH.dt.MESS_TYP_DE_BEZ,

     DB_DWH.dt.MESS_TYP_NR,

     (DB_DWH.dt.FKL_HIER_LVL  ||' -

     ')||DB_DWH.dt.FKL_DE_BEZ,

     COUNT(DB_DWH.dt.DIM_DAT_MAND  )(INTEGER), SUM(( CASE

     WHEN (DB_DWH.dt.MESSTYP_ID  = 1) THEN

     (DB_DWH.dt.ANZ_FZG) ELSE (NULL) END )),

     DB_DWH.dt.STD_NR - 1, DB_DWH.dt.STD_BEZ,

     DB_DWH.dt.YEAR_NR, DB_DWH.dt.Y_COR_NR,

     DB_DWH.dt.X_COR_NR, DB_DWH.dt.RICT_2_BEZ,

     DB_DWH.dt.RICT_1_BEZ, DB_DWH.dt.KT_BEZ,

     DB_DWH.dt.STR_BEZ, DB_DWH.dt.MESS_BEZ,

     DB_DWH.dt.MESS_NR_BEZ, (CASE WHEN

     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN

     ('boimg://Logi_ZZZ.gif') ELSE

     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')

     END), DB_DWH.dt.MAND_BEZ) to all AMPs.  Then we do a

     SORT to order Spool 1 by the sort key in spool field1 eliminating

     duplicate rows.  The size of Spool 1 is estimated with no

     confidence to be 7,945,871 rows (10,226,335,977 bytes).  The

     estimated time for this step is 45.30 seconds.

 11) 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 1 minute and 11 seconds.

N/A

Re: Aggregated Join Index on 2 systems

The stats are freshly collected, but not the same, e.g. step 2.2 (preparation for the join to AJI):

   with low confidence to be 16,836 rows  (AJI not used)

vs.

    no confidence to be 32,062 rows (AJI used)

Teradata Employee

Re: Aggregated Join Index on 2 systems

You're right, Dieter! On system 2 (no AJI) there are 3 small tables having STATS. On system 1 (AJI used) the tables have no STATS.

I dropped STATS on system 2. The Query is now running using AJI!

Curious, isn't it? Having STATS collected didn't help.

N/A

Re: Aggregated Join Index on 2 systems

Did you compare CPU/IO from DBQL on both systems, did the AJI actually use less/run faster?

There's a lot of "no confidence" in both explains, did you check for missing stats using DIAGNOSTIC HELPSTATS?