I wrote a blog earlier about [Rebuilding Indexes Online using DB02 | Rebuilding Index online using DB02] , in the blog I had mentioned that there is no option to rebuild partitioned indexes. So, I thought i'll follow that blog with this one wherein there are steps of rebuilding a partitioned index.
Let us revisit the unbalanced index problem that was mentioned in the blog mentioned above.
Problem - Unbalanced Partitioned Index
There might me some warnings such as:
These are partitioned indexes, they cannot be rebuilt using DB02 or using RSANAORA report.
The following is what we need to rebuild partitioned indexes.
The Solution - Using SQLPLUS
Prequisites/Assumptions: Start SQLPLUS:<br /><br />We login as ora<sid> into the system and start sqlplus by using a command:<br /><br />sqlplus "/ as sysdba"
You should see a similar output
0.1.
List Index Partitions:
Now that we are logged into sqlplus, we list the partitions of the affected index. To list the partitions we query the table dba_ind_partitions with the command as follows:
select index_name, partition_name from dba_ind_partitions where index_name = '<affected_index_name>';
As shown below, we will get the list of partitions:
Now as show above we have the partition_name , copy this name, however before we reubild the index partition, BW systems can have huge index partitions.
0.1.
Determine the Size:
This can be easily determined from SAPGUI, login into the SAP system and start DB02, now click on the Detailed Analysis button to bring up the following screen:
Here we provide the partition name, we may have to provide *asterisk as shown above, now click the green tick and you should get the following screen:
If the index partition is bigger in size, you may want to perform the activity on a non-working day or a quiet period.
0.1.
Rebuild Partition:
Now that we have determined its okay to rebuild the partition, we actually perform the rebuild using the following command:
alter index <index_name> rebuild partition <partition_name>;
The command will execute as shown below:
!https://weblogs.sdn.sap.com/weblogs/images/36110/im015.JPG|height=101|alt=Rebuild the partition|width=568|src=https://weblogs.sdn.sap.com/weblogs/images/36110/im015.JPG|border=0!</body>
