All Forums Database
Abhishektd 7 posts Joined 06/11
05 Feb 2016
Patitioning expression

Which of the following partitioning expression has a better performance while SELECT, INSERT operations ? Consider the table has data around couple of Terabytes.
PARTITION BY RANGE_N(LOAD_TIMESTAMP  BETWEEN TIMESTAMP '2015-01-01 00:00:00.000000' AND TIMESTAMP '3499-12-31 23:23:59.999999' EACH INTERVAL '1' DAY );
or
PARTITION BY RANGE_N(CAST(LOAD_TIMESTAMP  AS DATE AT LOCAL)  BETWEEN DATE '2015-01-01' AND '3499-12-31' EACH INTERVAL '1' DAY );

Thanks & regards,

Abhi

dnoeth 4628 posts Joined 11/04
08 Feb 2016

Hi Abhi,
both should be similar, resulting in daily partitions.
But you should limit the upper date to something like 2030 and add an another partition for your "until changed". Otherwise you'll waste a lot of disk space, as you get way more than 65535 partitions and the partition number will be 8 bytes instead of 2.

Dieter

You must sign in to leave a comment.