Joined: 11 Jan 2006 Posts: 66 Location: Lower Saxony (DE)
We’ve got a table that is – surprise, surprise – growing and growing over the last years and won’t stop growing in future: Today more than one mio rows, max. row length more than 8K, avg. row length more than 1K. So if we want to manage the increasing number of rows we’d like to partition the table (tablespace). So far so good.
The table contains one (self-referencing) foreign key constraint (Parent_ID DECIMAL(15, 0)) and we’d like to partition via this fk row. But only one eighth contains valid data, the other seven eighths ‘contain’ NULL.
Partitioning only via this fk column like
PARTITION BY ( Parent_ID)
( PARTITION 1 ENDING AT (999999999999999)
, PARTITION 2 ENDING AT ( MAXVALUE))
results the first eight with valid fk content in the first partition (as desired) but the seven eighths with Parent_ID NULL in a very large second partition. Not bad, but because of the lots of rows, this doesn’t bring a huge benefit in second partition.
Actually it’s not a big thing to add a second partitioning column. But how to declare the ending values? I.e.
PARTITION BY ( Parent_ID, Insert_Date )
( PARTITION 1 ENDING AT (999999999999999, MAXVALUE )
, PARTITION 2 ENDING AT ( MAXVALUE, ‘2010-12-31’)
, PARTITION 3 ENDING AT ( MAXVALUE, ‘2011-12-31’)
, PARTITION 4 ENDING AT ( MAXVALUE, ‘2012-12-31’)
, PARTITION 5 ENDING AT ( MAXVALUE, ‘2013-12-31’)
, PARTITION 6 ENDING AT ( MAXVALUE, ‘2014-12-31’)
, PARTITION 7 ENDING AT ( MAXVALUE, ‘2015-12-31’)
, PARTITION 8 ENDING AT ( MAXVALUE, ‘2016-12-31’)
, PARTITION 9 ENDING AT ( MAXVALUE, ‘2017-12-31’)
, PARTITION 10 ENDING AT ( MAXVALUE, MAXVALUE ))
is not allowed, results an SQL-Code -635 / SQL-State ’56.016’ :-(
Has anybody got an idea to solve this problem?
Thanks a lot for your helpful hints