Ask the Expert

Q: After a table and its local indexes have been defined using range partitioning with a default maxvalue partition, how can you add more partitions as new subsets of data are received?

A: Use an alter table statement to split the default data partitions, adding your new partition ranges. For example, to add a data partition for January 2008 data in the SALES table in the previous listing, issue the following command:

alter table sales
 split partition sales_max at (to_date('2008-02-01','YYYY-MM-DD'))
 into (partition sales_200801 tablespace sales_ts_200801,
       partition sales_max tablespace sales_ts_max);

This alter table command will result in the default index partition for sales_idx_l1 to also be split. However, it will use the data partition names (for example, sales_200801) and tablespaces (sales_ts_200801, for instance); remember in the local index example we explicitly specified the partition names and tablespaces for the index. Therefore, the partition names and tablespaces will need to be adjusted using alter index commands, as follows:

alter index sales_idx_11
 rename partition sales_200801 to sales_idx_200801;

alter index sales_idx_11
 rebuild partition sales_idx_200801 tablespace sales_ts_idx_200801;

alter index sales_idx_11
 rebuild partition sales_idx_max tablespace sales_ts_idx_max;