Thursday, January 30, 2014

Is my index/index (sub)partition unusable? N/A state indexes, Exadata

When you work with Exadata , we usually compress the data /the tables.. One of the method to compress the data in the tables, which the compress atrribute set on, is moving the table with "alter table move command" .. Note that, Exadata is a preferred system for large databases in which the tables are partitioned even subpartitioned. So in that case; you need move these partitions and subpartitions, too..

When the tables/partitions/subpartitions are moved, the indexes become invalid, as moving is a physical operation which causes the rowid stored in related indexes change..
So you need to rebuild the indexes , index partition and index partitions, accordingly...

To find the indexes those need to be rebuilt, you can use following queries.. The queries below are actually dynamic sqls which will produce the rebuild commands directly..

Sub partition rebuild:
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' parallel 8;' from dbA_ind_subpartitions where status not in ('USABLE','VALID')

Partition rebuild:
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' parallel 8;' from dbA_ind_partitions  where status not in ('USABLE','VALID')

Index rebuild:
select 'alter index '||owner||'.'||index_name||' rebuild parallel 8;' from dbA_indexes  where status not in ('USABLE','VALID')

The thing that me writing this post is the index status N/A.
There are indexes in N/A status and  N/A status does not mean that the index must be rebuilt..
You have to analyze this N/A status in levels. I mean, lets suppose dba_indexes = LEVEL1, dba_partitions=LEVEL2, dba_subpartitions=LEVEL3

So if you see a N/A state index in dba_indexes, check dba_ind_partitions..
And if you see a N/A state index partition in dba_ind_partitions check dba_ind_subpartitions :)
so check LEVEL1 -> LEVEL 2 -> LEVEL 3.
In brief, you dont rebuild every N/A state index you see :)

No comments :

Post a Comment