This is my opinion. I make partition to achieve speed and a goal: I will count the partitions. This is just example:

SELECT RANGE_N( EFF_END_DT BETWEEN DATE '2012-12-31' AND DATE '9999-12-31' EACH INTERVAL '7' DAY , NO RANGE, UNKNOWN) partitions

COUNT(*) AS cnt,

FROM table1

GROUP BY partitions

;

Grouping older ranges with less finer say

PARTITION BY RANGE_N (order_date BETWEEN DATE '2012-12-31'

AND DATE '2013-12-31'

EACH INTERVAL '3' MONTH,

DATE '2013-12-31'

AND DATE '2014-06-30'

EACH INTERVAL '7' DAY,

.........

SELECT *

FROM table1

WHERE your_date BETWEEN DATE '.....aaaa-12-31...' AND DATE '.....bbbb-12-31';

Raja K Thaw

My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1

Street Children suffer not by their fault. We can help them if we want.

All of our tables have effective start and end date columns (EFF_STRT_DT, EFF_END_DT). We want to create date partitions on some of our larger tables, and our fist instinct is to create the partition on EFF_END_DT, because we set that date to '9999-21-31' to represent "active" rows in our data warehouse.

Most of the time, we will actually be doing a BETWEEN comparison similar to the following

The table in the code snippet is defined with a partition statement of

PARTITION BY RANGE_N(EFF_END_DT BETWEEN DATE '2012-12-31' AND DATE '9999-12-31' EACH INTERVAL '7' DAY , NO RANGE, UNKNOWN)

and the SQL in the snippet shows this in the EXPLAIN plan:

3) We do an all-AMPs RETRIEVE step from 416678 partitions of

fpdw_bkup.pty_pi with a condition of (

"(fpdw_bkup.pty_pi.EFF_STRT_DT <= DATE '2014-03-31') AND

(fpdw_bkup.pty_pi.EFF_END_DT >= DATE '2014-03-31')") into Spool 1

(group_amps), which is built locally on the AMPs.

What I'm trying to determine is whether the partition I had in mind is worth it, given that we don't often use equi-joins with these date columns. Or is there a different way to do the partition or the SQL statement that would help me?

Thanks,

Robin