Archives of the TeradataForum

Message Posted: Thu, 08 Jul 2010 @ 12:18:02 GMT

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

I have a PPI table defined with a cast on a timestamp field which is producing inconsistent behavior.

     PARTITION BY RANGE_N( CAST((DW_END_DT ) AS DATE)
        BETWEEN DATE '2008-06-01' AND DATE '2010-12-31',
        DATE '9999-12-31' AND DATE '9999-12-31'
        EACH INTERVAL '1' MONTH);

If I run the below two queries the first uses the partitioning the second does not. I don t understand why?

     EXPLAIN SELECT COUNT(*)
             FROM "V_WOFDWVW"."F_VOL_DCSN" "Volume_Measures"
            WHERE "Volume_Measures"."DW_END_DT"  = '9999-12-31 00:00:00'

       2) Next, we do an all-AMPs SUM step to aggregate from a single
          partition of COGN_WOFDWRPT.F_VOL_DCSN in view V_WOFDWVW.F_VOL_DCSN
          with a condition of ("COGN_WOFDWRPT.F_VOL_DCSN in view
          V_WOFDWVW.F_VOL_DCSN.DW_END_DT = TIMESTAMP '9999-12-31 00:00:00'")


     EXPLAIN SELECT *
             FROM "V_WOFDWVW"."F_VOL_DCSN" "Volume_Measures"
            WHERE "Volume_Measures"."DW_END_DT"  = '9999-12-31 00:00:00'

       2) Next, we do an all-AMPs RETRIEVE step from
          COGN_WOFDWRPT.F_VOL_DCSN in view V_WOFDWVW.F_VOL_DCSN by way of an
          all-rows scan with a condition of ("COGN_WOFDWRPT.F_VOL_DCSN in
          view V_WOFDWVW.F_VOL_DCSN.DW_END_DT = TIMESTAMP '9999-12-31
          00:00:00'") into Spool 2 (group_amps), which is built locally on

Paul De Pons

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