Teradata query tunning

Database
Enthusiast

Teradata query tunning

explain
SELECT
  DIM_PFC_CUSTOMER_LIST_dim.DISTR_METHOD,
  DIM_PFC_CUSTOMER_LIST_dim.PFC_DESC,
  DIM_PFC_CUSTOMER_LIST_dim.KEY_ACCOUNT_DESC,
  DIM_PFC_CUSTOMER_LIST_dim.BANNER_DESC,
  DIM_PFC_CUSTOMER_LIST_dim.AREA_DESC,
  DIM_PFC_PRODUCT_dim.MANUFACTURER,
  DIM_PFC_PRODUCT_dim.BRAND_DESC,
  DIM_PFC_PRODUCT_dim.BRAND_SIZE_DESC,
  SEM_PFC_TPM.VIEW_DIM_PFC_PERIOD.YEAR_NO,
  SEM_PFC_TPM.VIEW_DIM_PFC_PERIOD.PERIOD_NO,
  SEM_PFC_TPM.VIEW_DIM_PFC_WEEK.WEEK_NO,
  sum(case when  DIM_PFC_PROMOTION_dim.status_desc = 'Closed' then FACT_PFC_PROMOTION_FORECAST_LE.Matched_amt else case when DIM_PFC_PROMOTION_dim.promotion_key = DIM_PFC_PROMOTION_dim.par_promo_key then FACT_PFC_PROMOTION_FORECAST_LE.GIP_ACCRUAL else
 ( FACT_PFC_PROMOTION_FORECAST_LE.EVT_SPEND_PROJ_BASE+FACT_PFC_PROMOTION_FORECAST_LE.EVT_SPEND_PROJ_DISCR+FACT_PFC_PROMOTION_FORECAST_LE.EVT_SPEND_PROJ_ADJ) end end ),
  DIM_PFC_PROMOTION_dim.PROMOTION_ID,
  DIM_PFC_PROMOTION_dim.PROMOTION_DESC,
  DIM_PFC_PROMOTION_dim.START_DATE,
  DIM_PFC_PROMOTION_dim.END_DATE,
  DIM_PFC_PROMOTION_dim.SHIP_START_DT,
  DIM_PFC_PROMOTION_dim.SHIP_END_DT,
  DIM_PFC_PROMOTION_dim.TACTIC_DESC,
  DIM_PFC_PROMOTION_dim.SUB_TACTIC,
  DIM_PFC_PROMOTION_dim.STATUS_DESC,
  DIM_PFC_PROMOTION_dim.EVT_QTY,
  DIM_PFC_PROMOTION_dim.FEAT_PRICE_UNIT
FROM
  SEM_PFC_TPM.DIM_PFC_CUSTOMER_LIST  DIM_PFC_CUSTOMER_LIST_dim,
  SEM_PFC_TPM.DIM_PFC_PRODUCT  DIM_PFC_PRODUCT_dim,
  SEM_PFC_TPM.VIEW_DIM_PFC_PERIOD,
  SEM_PFC_TPM.VIEW_DIM_PFC_WEEK,
  SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST  FACT_PFC_PROMOTION_FORECAST_LE,
  SEM_PFC_TPM.DIM_PFC_PROMOTION  DIM_PFC_PROMOTION_dim,
  SEM_PFC_TPM.DIM_PFC_DAY  DIM_PFC_DAY_PROMO
WHERE
  ( SEM_PFC_TPM.VIEW_DIM_PFC_PERIOD.PERIOD_ID=SEM_PFC_TPM.VIEW_DIM_PFC_WEEK.PERIOD_ID  )
  AND  ( DIM_PFC_DAY_PROMO.WEEK_ID=SEM_PFC_TPM.VIEW_DIM_PFC_WEEK.WEEK_ID  )
  AND  ( DIM_PFC_DAY_PROMO.TIME_KEY=FACT_PFC_PROMOTION_FORECAST_LE.DAY_KEY  )
  AND  ( DIM_PFC_PRODUCT_dim.PRODUCT_KEY=FACT_PFC_PROMOTION_FORECAST_LE.PRODUCT_KEY  )
  AND  ( DIM_PFC_CUSTOMER_LIST_dim.CUST_LIST_KEY=FACT_PFC_PROMOTION_FORECAST_LE.CUST_LIST_KEY  )
  AND  ( DIM_PFC_PROMOTION_dim.PROMOTION_KEY=FACT_PFC_PROMOTION_FORECAST_LE.PROMOTION_KEY  )
  AND  ( FACT_PFC_PROMOTION_FORECAST_LE.TIME_TYPE_CD='LE'  )
  AND 
  (
   ( ( ( DIM_PFC_PROMOTION_dim.CATEGORY ) ) IN 'Volume'  )
   AND
   ( cast(( DIM_PFC_PROMOTION_dim.WEEK_START_DATE ) as date) between date- interval '6' day and date+ interval '112' day  )
   AND
      ( ( DIM_PFC_PROMOTION_dim.TACTIC_CODE ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
      ( ( DIM_PFC_PROMOTION_dim.STATUS_CODE ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
      ( ( DIM_PFC_CUSTOMER_LIST_dim.DISTR_METHOD ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
      ( ( DIM_PFC_CUSTOMER_LIST_dim.PFC_CODE ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
      ( ( DIM_PFC_PRODUCT_dim.MANUFACTURER ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
      ( ( DIM_PFC_CUSTOMER_LIST_dim.AREA_CODE ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
      ( ( DIM_PFC_CUSTOMER_LIST_dim.KEY_ACCOUNT_CODE ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
      AND
   ( ( DIM_PFC_CUSTOMER_LIST_dim.BANNER_CODE ) IN ('-ALL-')  OR  '-ALL-' IN ('-ALL-')  )
  )
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  13,
  14,
  15,
  16,
  17,
  18,
  19,
  20,
  21,
  22,
  23

  1) First, we lock PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in view
     SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST for access, we lock
     PFC_TPM_D1_DATA.DIM_PFC_PROMOTION in view
     SEM_PFC_TPM.DIM_PFC_PROMOTION for access, we lock
     PFC_TPM_D1_DATA.DIM_PFC_PRODUCT in view
     SEM_PFC_TPM.DIM_PFC_PRODUCT for access, we lock
     PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST in view
     SEM_PFC_TPM.DIM_PFC_CUSTOMER_LIST for access, and we lock
     PFC_TPM_D1_DATA.DIM_PFC_DAY in view SEM_PFC_TPM.VIEW_DIM_PFC_WEEK
     for access.
  2) Next, we do an all-AMPs SUM step to aggregate from
     PFC_TPM_D1_DATA.DIM_PFC_DAY in view SEM_PFC_TPM.VIEW_DIM_PFC_WEEK
     by way of an all-rows scan with a condition of ("(NOT
     (PFC_TPM_D1_DATA.DIM_PFC_DAY in view
     SEM_PFC_TPM.VIEW_DIM_PFC_WEEK.PERIOD_ID IS NULL )) AND (NOT
     (PFC_TPM_D1_DATA.DIM_PFC_DAY in view
     SEM_PFC_TPM.VIEW_DIM_PFC_WEEK.WEEK_ID IS NULL ))")
     , grouping by field1 ( PFC_TPM_D1_DATA.DIM_PFC_DAY.FIS_WEEK_KEY
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.FIS_PERIOD_KEY
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.FIS_YEAR_KEY
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.FISCAL_WK_START_DATE
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.FISCAL_WK_CLOSE_DATE
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PD_START_DT
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PD_CLOSE_DT
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.HALF_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.QTR_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.TRI_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PERIOD_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.WEEK_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.HALF_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.QTR_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.TRI_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PERIOD_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.WEEK_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.WEEK_OF_YEAR
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_HALF
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_QTR
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_WEEK
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_PERIOD
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PERIOD_WEEK
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.KEY_WEEK
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.CY_CP ,PFC_TPM_D1_DATA.DIM_PFC_DAY.CY
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.LY).  Aggregate Intermediate Results
     are computed globally, then placed in Spool 4.  The size of Spool
     4 is estimated with no confidence to be 4,386 rows (1,350,888
     bytes).  The estimated time for this step is 0.04 seconds.
  3) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
          way of an all-rows scan into Spool 2 (used to materialize
          view, derived table, table function or table operator
          VIEW_DIM_PFC_WEEK) (all_amps), which is built locally on the
          AMPs.  The size of Spool 2 is estimated with no confidence to
          be 4,386 rows (140,352 bytes).  The estimated time for this
          step is 0.01 seconds.
       2) We do an all-AMPs SUM step to aggregate from
          PFC_TPM_D1_DATA.DIM_PFC_DAY in view
          SEM_PFC_TPM.VIEW_DIM_PFC_PERIOD by way of an all-rows scan
          with a condition of ("NOT (PFC_TPM_D1_DATA.DIM_PFC_DAY in
          view SEM_PFC_TPM.VIEW_DIM_PFC_PERIOD.PERIOD_ID IS NULL)")
          , grouping by field1 (
          PFC_TPM_D1_DATA.DIM_PFC_DAY.FIS_PERIOD_KEY
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.FIS_YEAR_KEY
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PD_START_DT
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PD_CLOSE_DT
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_ID
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.HALF_ID
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.QTR_ID
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.TRI_ID
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PERIOD_ID
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.CY_CP
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_NO
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.HALF_NO
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.QTR_NO
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.TRI_NO
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PERIOD_NO
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_HALF
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_QTR
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_PERIOD
          ,PFC_TPM_D1_DATA.DIM_PFC_DAY.CY) locking for access.
          Aggregate Intermediate Results are computed globally, then
          placed in Spool 7.  The size of Spool 7 is estimated with no
          confidence to be 4,386 rows (811,410 bytes).  The estimated
          time for this step is 0.03 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of
     an all-rows scan into Spool 1 (used to materialize view, derived
     table, table function or table operator VIEW_DIM_PFC_PERIOD)
     (all_amps), which is built locally on the AMPs.  The size of Spool
     1 is estimated with no confidence to be 4,386 rows (135,966 bytes).
     The estimated time for this step is 0.01 seconds.
  5) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
          way of an all-rows scan with a condition of ("NOT
          (VIEW_DIM_PFC_PERIOD.PERIOD_ID IS NULL)") into Spool 12
          (all_amps), which is duplicated on all AMPs.  The size of
          Spool 12 is estimated with no confidence to be 131,580 rows (
          3,026,340 bytes).  The estimated time for this step is 0.01
          seconds.
       2) We do an all-AMPs RETRIEVE step from
          PFC_TPM_D1_DATA.DIM_PFC_PROMOTION in view
          SEM_PFC_TPM.DIM_PFC_PROMOTION by way of an all-rows scan with
          a condition of ("((CAST((PFC_TPM_D1_DATA.DIM_PFC_PROMOTION in
          view SEM_PFC_TPM.DIM_PFC_PROMOTION.WEEK_START_DATE) AS
          DATE))>= DATE '2014-10-14') AND
          (((PFC_TPM_D1_DATA.DIM_PFC_PROMOTION in view
          SEM_PFC_TPM.DIM_PFC_PROMOTION.CATEGORY )= 'Volume') AND
          ((CAST((PFC_TPM_D1_DATA.DIM_PFC_PROMOTION in view
          SEM_PFC_TPM.DIM_PFC_PROMOTION.WEEK_START_DATE) AS DATE))<=
          DATE '2015-02-09'))") into Spool 13 (all_amps) fanned out
          into 4 hash join partitions, which is duplicated on all AMPs.
          The size of Spool 13 is estimated with no confidence to be
          177,570 rows (52,383,150 bytes).  The estimated time for this
          step is 0.25 seconds.
       3) We do an all-AMPs RETRIEVE step from
          PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in view
          SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST by way of an all-rows
          scan with a condition of (
          "((PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in view
          SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST.TIME_TYPE_CD )= 'LE')
          AND ((NOT (PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in
          view SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST.DAY_KEY IS NULL
          )) AND ((NOT (PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in
          view SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST.PRODUCT_KEY IS
          NULL )) AND ((NOT
          (PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in view
          SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST.CUST_LIST_KEY IS NULL
          )) AND (NOT (PFC_TPM_D1_DATA.FACT_PFC_PROMOTION_FORECAST in
          view SEM_PFC_TPM.FACT_PFC_PROMOTION_FORECAST.PROMOTION_KEY IS
          NULL )))))") into Spool 14 (all_amps) fanned out into 4 hash
          join partitions, which is built locally on the AMPs.  The
          input table will not be cached in memory, but it is eligible
          for synchronized scanning.  The size of Spool 14 is estimated
          with no confidence to be 3,663,613 rows (399,333,817 bytes).
          The estimated time for this step is 46.90 seconds.
       4) We do an all-AMPs RETRIEVE step from
          PFC_TPM_D1_DATA.DIM_PFC_DAY in view SEM_PFC_TPM.DIM_PFC_DAY
          by way of an all-rows scan with a condition of ("NOT
          (PFC_TPM_D1_DATA.DIM_PFC_DAY in view
          SEM_PFC_TPM.DIM_PFC_DAY.WEEK_ID IS NULL)") locking for access
          into Spool 15 (all_amps), which is duplicated on all AMPs.
          The size of Spool 15 is estimated with high confidence to be
          175,410 rows (3,683,610 bytes).  The estimated time for this
          step is 0.02 seconds.
  6) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an
     all-rows scan, which is joined to Spool 14 (Last Use) by way of an
     all-rows scan.  Spool 13 and Spool 14 are joined using a hash join
     of 4 partitions, with a join condition of ("PROMOTION_KEY =
     PROMOTION_KEY").  The result goes into Spool 16 (all_amps), which
     is built locally on the AMPs.  The size of Spool 16 is estimated
     with no confidence to be 274,778 rows (106,339,086 bytes).  The
     estimated time for this step is 0.62 seconds.
  7) We do an all-AMPs RETRIEVE step from
     PFC_TPM_D1_DATA.DIM_PFC_PRODUCT in view
     SEM_PFC_TPM.DIM_PFC_PRODUCT by way of an all-rows scan with no
     residual conditions into Spool 17 (all_amps), which is duplicated
     on all AMPs.  The size of Spool 17 is estimated with high
     confidence to be 263,280 rows (21,588,960 bytes).  The estimated
     time for this step is 0.06 seconds.
  8) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an
     all-rows scan, which is joined to Spool 16 (Last Use) by way of an
     all-rows scan.  Spool 15 and Spool 16 are joined using a single
     partition hash join, with a join condition of ("TIME_KEY = DAY_KEY").
     The result goes into Spool 18 (all_amps), which is built locally
     on the AMPs.  The size of Spool 18 is estimated with no confidence
     to be 274,778 rows (106,339,086 bytes).  The estimated time for
     this step is 0.34 seconds.
  9) We do an all-AMPs RETRIEVE step from
     PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST in view
     SEM_PFC_TPM.DIM_PFC_CUSTOMER_LIST by way of an all-rows scan with
     no residual conditions into Spool 19 (all_amps), which is
     duplicated on all AMPs.  The size of Spool 19 is estimated with
     high confidence to be 86,250 rows (11,988,750 bytes).  The
     estimated time for this step is 0.04 seconds.
 10) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an
     all-rows scan, which is joined to Spool 18 (Last Use) by way of an
     all-rows scan.  Spool 17 and Spool 18 are joined using a single
     partition hash join, with a join condition of ("PRODUCT_KEY =
     PRODUCT_KEY").  The result goes into Spool 20 (all_amps), which is
     built locally on the AMPs.  The size of Spool 20 is estimated with
     no confidence to be 274,778 rows (123,100,544 bytes).  The
     estimated time for this step is 0.39 seconds.
 11) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
     all-rows scan, which is joined to Spool 20 (Last Use) by way of an
     all-rows scan.  Spool 19 and Spool 20 are joined using a single
     partition hash join, with a join condition of ("CUST_LIST_KEY =
     CUST_LIST_KEY").  The result goes into Spool 21 (all_amps) fanned
     out into 10 hash join partitions, which is built locally on the
     AMPs.  The size of Spool 21 is estimated with no confidence to be
     274,778 rows (155,524,348 bytes).  The estimated time for this
     step is 0.46 seconds.
 12) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an
     all-rows scan, which is joined to Spool 2 (Last Use) by way of an
     all-rows scan with a condition of ("NOT (VIEW_DIM_PFC_WEEK.WEEK_ID
     IS NULL)").  Spool 12 and Spool 2 are joined using a dynamic hash
     join, with a join condition of ("PERIOD_ID = PERIOD_ID").  The
     result goes into Spool 22 (all_amps) fanned out into 10 hash join
     partitions, which is duplicated on all AMPs.  The size of Spool 22
     is estimated with no confidence to be 8,714,160 rows (226,568,160
     bytes).  The estimated time for this step is 0.58 seconds.
 13) 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 scan.  Spool 21 and Spool 22 are joined using a hash join
     of 10 partitions, with a join condition of ("WEEK_ID = WEEK_ID").
     The result goes into Spool 11 (all_amps), which is built locally
     on the AMPs.  The result spool file will not be cached in memory.
     The size of Spool 11 is estimated with no confidence to be
     148,092,852 rows (84,857,204,196 bytes).  The estimated time for
     this step is 3 minutes and 6 seconds.
 14) We do an all-AMPs SUM step to aggregate from Spool 11 (Last Use)
     by way of an all-rows scan , grouping by field1 (
     PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST.DISTR_METHOD
     ,PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST.PFC_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST.KEY_ACCOUNT_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST.BANNER_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_CUSTOMER_LIST.AREA_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_PRODUCT.MANUFACTURER
     ,PFC_TPM_D1_DATA.DIM_PFC_PRODUCT.BRAND_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_PRODUCT.BRAND_SIZE_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.YEAR_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.PERIOD_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_DAY.WEEK_NO
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.PROMOTION_ID
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.PROMOTION_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.START_DATE
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.END_DATE
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.SHIP_START_DT
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.SHIP_END_DT
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.TACTIC_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.SUB_TACTIC
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.STATUS_DESC
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.EVT_QTY
     ,PFC_TPM_D1_DATA.DIM_PFC_PROMOTION.FEAT_PRICE_UNIT).  Aggregate
     Intermediate Results are computed globally, then placed in Spool
     23.  The aggregate spool file will not be cached in memory.  The
     size of Spool 23 is estimated with no confidence to be 111,069,639
     rows (191,372,987,997 bytes).  The estimated time for this step is
     2 hours and 43 minutes.
 15) We do an all-AMPs RETRIEVE step from Spool 23 (Last Use) by way of
     an all-rows scan into Spool 9 (group_amps), which is built locally
     on the AMPs.  The result spool file will not be cached in memory.
     The size of Spool 9 is estimated with no confidence to be
     111,069,639 rows (101,406,580,407 bytes).  The estimated time for
     this step is 2 minutes and 37 seconds.
  -> The contents of Spool 9 are sent back to the user as the result of
     statement 1.  The total estimated time is 2 hours and 49 minutes.

Hi,

I have a  teradata query generated by BO for reporting. The performance of the report/query is not good.

I tried to collect stats on all the tables in the query. Still I see a lot of estimations with "no confidence" in the  explain plan.

Please let me know if I am missing something here.

Thanks in advance!!

6 REPLIES
Enthusiast

Re: Teradata query tunning

Hi Arpit,

Right side of the query is not fully viewable but few things you can try:

1) Did you collect stats on only the PI columns or both PI columns and the join columns used in the query?

2) 1 more reason could be i am seeing few dimension tables. For ex: Product, Period, Week etc... If period, week is not specific to an organization you can aviod those small table joins.

3) Split up the query. Instead of joining all the tables at one time. Load some large volume table first to a work/volatile table and then join the work/volatile table to load to final table.

Enthusiast

Re: Teradata query tunning

Hi Ravi,

I collected multi column stats on the tables on PI and joining columns.

This is a query that is generated by BO and I cannot tweek the query.

I am supposed implement the indexing or collect stats strategies to improve the perforance for future runs.

Thanks a lot for your response.

Enthusiast

Re: Teradata query tunning

My suggestion is to tweak at BO side, join properties. Try to replace IN with = wherever possible. Think of how you can recode this : IN ('-ALL-'OR  '-ALL-' IN ('-ALL-')  ). What is the business requirement of this code and convert it accordingly.

Enthusiast

Re: Teradata query tunning

Just small suggestions from you guys, Can you please help us

I have a join as follows.

(AND a.col1=b.col2 or col1 in (1,3,4,6,8,9)), this join is being run for long time and went for PRODUCT join…Can you suggest us with good stuff.

Teradata Employee

Re: Teradata query tunning

What are the definitions & size of A and B?

Enthusiast

Re: Teradata query tunning

Sorry Adeel, I should have been more specfic, Please find the requested details 

Table A-->67,315,935

Table B--> 81,415,754

and ( A.proc_type_cd = B.proc_type_cd 
or B.proc_type_cd in ('1','2','3','4','5','6')
)
-show table tables.a
PRIMARY INDEX ( REF_NO ,INV_DT )
PARTITION BY RANGE_N(INV_DT BETWEEN DATE '2010-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH ,
NO RANGE)
INDEX ( OWNRSHP_ID )
INDEX ( PB_OWNRSHP_ID );

--show table tables.b
PRIMARY INDEX ( REF_NO ,INV_DT )
PARTITION BY RANGE_N(INV_DT BETWEEN DATE '2010-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH ,
NO RANGE)
INDEX ( PSF_DT );

Kinldy let me know if you need any details. Thank you in advance for your help