Home » RDBMS Server » Server Administration » Table Partition - Rebuild local Indexes
Table Partition - Rebuild local Indexes [message #237694] Tue, 15 May 2007 08:36 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Table Partition - Rebuild local Indexes [message #237715 is a reply to message #237710] Tue, 15 May 2007 09:05 Go to previous message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Bryan,

I have experience in production database for the same size of tables and used to handle 20 to 30 gig for single partition.

Let me know if you need any help.

Regards,
Harsh
Previous Topic: dbstart
Next Topic: Drop Temporary Tablespace
Goto Forum:
  


Current Time: Fri Dec 09 09:22:43 CST 2016

Total time taken to generate the page: 0.15997 seconds