Archives of the TeradataForum

Message Posted: Thu, 19 Nov 2009 @ 13:47:39 GMT

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

Hi,

After migration from 6.0 to 6.2, I've experienced a following problem:

Have a large table partitioned by startday DECIMAL(8,0) - i.e. storing Jan 1st 2009 as 20090101 - with partitioning expression

     (1) PARTITION BY RANGE_N((CAST((startday ) AS DATE))- INTERVAL '1900' YEAR
         BETWEEN DATE '2007-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY , UNKNOWN)

The reason for startday being decimal and not date is a compatibility with existing reports which use decimal format for querying using this column in the where constraint.

Previously on 6.0, query as

     Select * from table_a
     where startday between 20090101 and 20090201

Was correctly accessing only the selected subset of partitions.

Now on 6.2, the behavior is different;

When using

     Select * from table_a
     where startday = 20090101

it (still) does access single partition access only.

However for

     Select * from table_a
     where startday between 20090101 and 20090201

It now (on 6.2) does a full table scan (all partitions).

Even more strange, even for

     Select * from table_a
     where startday between 20090101 and 20090101

(the same value used for between x and x), it also does a full table scan across all partitions!

I have tested it for startday INTEGER (changing from the "legacy" DECIMAL(8,0) to INTEGER would require only very limited testing of the connecting applications) with partitioning expression

     (2) PARTITION BY  RANGE_N((startday - 20070000)
         BETWEEN 00000101 AND 0061231 EACH 1, UNKNOWN);

And it works properly (only x partitions scanned). For a startday changed to DATE type, it also seems to work properly. But even with INTEGER used with the original (1) partitioning expression, it has the same problem - doing a full table, all partitions scan - as with the DECIMAL type (i.e. the problem seems to be in the CAST INTEGER/DECIMAL AS DATE partitioning expression)

However, when the new (2) partitioning expression is used with the original DECIMAL(8,0) type, the CREATE TABLE statement return an error 3732: The facility of a test value with data type other than integer or DATE has not been implemented yet.

The problem still is that using the numerical function in the new (2) PARTITION BY definition leaves me with smaller total date range compared to CAST AS DATE / INTERVAL solution (1) that was in originally in place.

Has anyone experience the same problem, or is this a bug in 6.2? Or a standard behavior from 6.2 upwards? Or, uhm, did I miss something?

Thanks in advance,

Martin

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