| Table Partition - Rebuild local Indexes [message #237694] |
Tue, 15 May 2007 08:36  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Here is sample exercise with range partition and local index.
SQL> select table_name, partition_name, tablespace_name from USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------- ---------------
RBN_MAIN BEFORE_FEB OWB_TAR
RBN_MAIN BEFORE_MAR OWB_TAR
RBN_MAIN ONORAFTER_MAR OWB_TAR
SQL> select index_name, partition_name, tablespace_name, status from USER_IND_PARTITIONS ;
INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ --------------- --------------- --------
RBN_MAIN_RM BEFORE_MAR INDEX_TS USABLE
RBN_MAIN_RM BEFORE_FEB INDEX_TS USABLE
RBN_MAIN_RM ONORAFTER_MAR INDEX_TS USABLE
SQL> alter table rbn_main
2 split partition onorafter_mar
3 at (TO_DATE('01-APR-2006','DD-MON-YYYY'))
4* INTO (PARTITION before_APR, PARTITION onorafter_Apr)
SQL> /
Table altered.
SQL> select table_name, partition_name, tablespace_name from USER_TAB_PARTITIONS ;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------- ---------------
RBN_MAIN BEFORE_FEB OWB_TAR
RBN_MAIN BEFORE_MAR OWB_TAR
RBN_MAIN BEFORE_APR OWB_TAR
RBN_MAIN ONORAFTER_APR OWB_TAR
SQL> select index_name, partition_name, tablespace_name, status from USER_IND_PARTITIONS;
INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ --------------- --------------- --------
RBN_MAIN_RM BEFORE_MAR INDEX_TS USABLE
RBN_MAIN_RM BEFORE_FEB INDEX_TS USABLE
RBN_MAIN_RM BEFORE_APR INDEX_TS UNUSABLE
RBN_MAIN_RM ONORAFTER_APR INDEX_TS UNUSABLE
SQL> ALTER TABLE rbn_main MODIFY PARTITION ONORAFTER_APR REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> ALTER TABLE rbn_main MODIFY PARTITION BEFORE_APR REBUILD UNUSABLE LOCAL INDEXES ;
Table altered.
SQL> select index_name, partition_name, tablespace_name, status from USER_IND_PARTITIONS;
INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ --------------- --------------- --------
RBN_MAIN_RM BEFORE_MAR INDEX_TS USABLE
RBN_MAIN_RM BEFORE_FEB INDEX_TS USABLE
RBN_MAIN_RM BEFORE_APR INDEX_TS USABLE
RBN_MAIN_RM ONORAFTER_APR INDEX_TS USABLE
SQL>
Here, whenever I split I need to rebuild the indexes.
Is there a better way of handling. i.e without the need to rebuild the indexes.
Brayan.
|
|
|
|
| Re: Table Partition - Rebuild local Indexes [message #237701 is a reply to message #237694] |
Tue, 15 May 2007 08:48  |
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
|
Senior Member |
|
|
Hi Bryan,
It seems to be good, You have one more alternative.
Instead of split add partition every first/last week of current month for next month.
I dont like your naming convention.. it may suits your application but I would suggest to go with below one that will also help for future requirements also.
For eg.
You have partitions like
JAN_07
FEB_07
MAR_07
APR_07
MAY_07 and for all else you have partition like MAXPARTITION at this point of time, and for next month you can add one more partition (i.e for June )either first/last week of May.
For eg.
ALTER TABLE <tab_name> ADD PARTITION <part_name(JUNE_07)> VALUE LESS THAN <(TO_DATE('01-JUL-2007','DD-MON-YYYY'))> TABLESPACE <tbs_name>;
By adding that will also create index, but it's empty partition so it won't take more then few seconds to rebuid indexes.
Regards,
Harsh
|
|
|
|
| Re: Table Partition - Rebuild local Indexes [message #237710 is a reply to message #237694] |
Tue, 15 May 2007 09:00 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi Harshad,
Thanks for your suggestions.
Regarding naming conventions its more of application point of view. Anyway its just a test. Definately, I'll have someother naming conventions in production and which will be meaningful.
This is a existing table with 200+ GB of data. Defenately I'll add a partition after completing the current partitioning job for next months data.
Its bit difficult to rebuild now and then in production.
I'm testing....
Brayan
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
