Rebuild one partition of an index

Today one of the our developer asked me that, one query works very slowly. After investigation I have found out plan of the query is incorrect and query use substr function which incorrect place.

While using select I`ve received ORA-01502. What is the this error?

ORA-01502: index 'string.string' or partition of such index is in unusable state

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
   

I am going to check my partition indexes which is unusable state.


SQL> select * from DBA_IND_PARTITIONS

where index_name = 'MY_ARCH_TEST_DB_IDX2' and status <> 'USABLE'


Ups! Found. P2008_201010 partition is unusable state. Checked how many rows has in this partition:


SQL> select count(1) from my_arch_test partition (P2008_201010)

126339601 rows.


Let me note, my table`s size approximate 27 GB. Not big, not little :)

Partitioned in_date column, and has 2 partitioned indexes, in_date and subscriber_id.

Checked when table`s statistics was taken and are those indexes valid. Yesterday gathered statistic.


Now, let`s rebuild partition index.

SQL> alter index MY_ARCH_TEST_DB_IDX2 rebuild partition P2008_201010 online;