 |
|
alter table split partition Tips
Oracle Tips by Burleson Consulting
November 12, 2007
|
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:
-
We created table with single
partition on tablespace.
-
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
sytax:
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: MOSC note
199623.1 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.
