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 );
PARTITION BY RANGE_N(CAST(LOAD_TIMESTAMP AS DATE AT LOCAL) BETWEEN DATE '2015-01-01' AND '3499-12-31' EACH INTERVAL '1' DAY );
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.