Re: Partition Type
Assuming you can't alter the id's to be integers, I would probably
try adding creation date to the primary key
primary key (id,creationdate)
then range partitioning by date (assuming more resent info gets the most usage)
Note: using range partitions with dates requires maint in order to keep useful
knowledge of what's in a partition. If you dont' need that, you could
use a hash partition, with 12 partitions.
alter table selling_trigger_story_hit
partition by range (extract(year_month from creationdate))
(
partition p_archive values less than (200701),
partition p_lastyear values less than (200801),
partition p_cur_jan values less than (200802),
partition p_cur_feb values less than (200803),
partition p_cur_mar values less than (200804),
partition p_cur_apr values less than (200805),
partition p_cur_may values less than (200806),
partition p_cur_jun values less than (200807),
partition p_cur_jul values less than (200808),
partition p_cur_aug values less than (200809),
partition p_cur_sep values less than (200810),
partition p_cur_oct values less than (200811),
partition p_cur_nov values less than (200812),
partition p_cur_dec values less than (200901),
partition p_future values less than (MAXVALUE)
)
If monthly data is still too much per partition, then you could subpartition
via hash by day (not exactly precise, as there are not always 30 days in a month)
Note: this will create about (15 * 30 paritions), alot, but still within the
1024 limit.
alter table selling_trigger_story_hit
partition by range (extract(year_month from creationdate))
subpartition by hash (dayofmonth(creationdate))
subpartitions 30
(
partition p_archive values less than (200701),
partition p_lastyear values less than (200801),
partition p_cur_jan values less than (200802),
partition p_cur_feb values less than (200803),
partition p_cur_mar values less than (200804),
partition p_cur_apr values less than (200805),
partition p_cur_may values less than (200806),
partition p_cur_jun values less than (200807),
partition p_cur_jul values less than (200808),
partition p_cur_aug values less than (200809),
partition p_cur_sep values less than (200810),
partition p_cur_oct values less than (200811),
partition p_cur_nov values less than (200812),
partition p_cur_dec values less than (200901),
partition p_future values less than (MAXVALUE)
)