suggestions for 10g partitioning when 11g is soon to come
Posted on 2011-10-17
I would like my 11g table to look like this...
CREATE TABLE mytable (
id number NOT NULL enable,
transaction_dtm timestamp default systimestamp not null enable,
part_key as (mod(extract(month from transaction_dtm), 6)) )
partition by range (part_key)
(Partition acl_aud_mes_part_1 values less than (1),
partition ACL_AUD_MES_PART_2 values less than (2),
Partition acl_aud_mes_part_3 values less than (3),
Partition acl_aud_mes_part_4 values less than (4),
Partition acl_aud_mes_part_5 values less than (5),
Partition acl_aud_mes_part_6 values less than (6)
but we are on 10g beyond the first production deployment of this table.
Any suggestions for a table deployment to 10g followed by an alter in 11g that will bring it around to look like this?
I can't partition on a virtual column in 10g. I'm guessing I would create a real column in 10g and partition on that, but how would I alter the table once 11g is available to switch it to be a virtual column? would I have to rename the table, create the table I want, insert all the data into the new table, drop the old table?