alter table split partition Tips Oracle Database Tips by Donald BurlesonNovember 12, 2015

Question:  I have a question about the use of alter table split partition.  Here is what we did trying to use the alter table split partition command:

1. We created table with single partition on tablespace.

2. We created local index on this single partition on tablespace.

After splitting the partition, we found that both data partitions and index partitions are on the same tablespace specified for the following alter table split partition syntax:

ALTER TABLE
   <tbl-name>
SPLIT PARTITION ... INTO... TABLESPACE <tblspace-name>.

 

ALTER TABLE
   my_table
SPLIT PARTITION
   my_table_part
AT (3)
INTO (PARTITION
    my_table_part_1,
PARTITION
    my_table_part_2);

The problem here is after splitting the table with alter table <tbl-name> split partition, Oracle also splits the local indexes accordingly but moves the indexes, originally defined from different tablespace, to the same tablespace of the underlying table. The steps I did were:

1. Create tables with single partition in one (data) tablespace

2. Create local indexes based on the single partition (above) in another (index) tablespace

3. Perform partition splitting on single partition to create multiple partitions using the alter table <tbl-name> split partition command.

After splitting the partition, Oracle automatically splits the local indexes accordingly as well but have both data and indexes in the originally defined tablespace.

We have reviewed the Oracle documentation on the use of alter table split partition in an attempt to resolve the issue.  With regard to the local indexes for the new partition and for the existing partition from which rows were redistributed, these are marked UNUSABLE and must be rebuilt.

Thus, after using the alter table split partition process, the new index partitions get their attributes from the original partition.  Unless otherwise specified, new index partitions are stored in the default tablespace of the partition being split.  If the original index partition has not got a default tablespace, the tablespace of the new underlying table partitions will be used.

Dr, Hall has these examples of using the alter table split partition syntax:

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

Is there anyway to achieve the same partition splitting as seen in the alter table <tbl-name> split partition command, but still have the data and indexes on a separate tablespaces?

Answer:  Oracle says that unless you specify update global indexes, any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt after using the alter table <tbl-name> split partition command.

Hence, you can put back the index, after issuing the alter table <tbl-name> split partition command, with an index rebuild command:

alter index iii rebuild tablespace ttt;

Most DBAs will place indexes into a separate tablespace for easier management and reporting rather than for improved performance.

The SPLIT PARTITION option divides a partition into two partitions, each with a new segment, new physical attributes, and new initial extents. The segment associated with the old partition is discarded.

This statement also performs a matching split on the corresponding partition in each local index defined on the table. The index partitions are split even if they are marked unusable.

With the exception of the TABLESPACE attribute, the physical attributes of the LOCAL index partition being split are used for both new index partitions. If the parent LOCAL index lacks a default TABLESPACE attribute, new LOCAL index partitions will reside in the same tablespace as the corresponding newly created partitions of the underlying table.

If you do not specify physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE) for the new partitions, the current values of the partition being split are used as the default values for both partitions.

If partition_name is not empty, SPLIT PARTITION marks all affected index partitions as unusable. This includes all global index partitions, as well as the local index partitions that result from the split.

The PARALLEL clause on SPLIT PARTITION does not change the default PARALLEL attributes of table.

The following example splits the old partition station5, thereby creating a new partition for station9:

ALTER TABLE trains
  SPLIT PARTITION STATION5 AT ( '50-001' )
  INTO (
    PARTITION station5 TABLESPACE train009 (MINEXTENTS 2),
    PARTITION station9 TABLESPACE train010 )
  PARALLEL ( DEGREE 9 );

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.