Index Rebuild not working

[Former Member]

Hi Experts,

I received a MISSING_INDEX warning in my DBcheck as it was unusable.

SQL> select TABLE_NAME,INDEX_TYPE,UNIQUENESS,PARTITIONED "PAR",status from dba_indexes where index_name = '/BIC/B0000516000KE';

TABLE_NAME INDEX_TYPE UNIQUENES PAR STATUS

/BIC/B0000516000 NORMAL NONUNIQUE NO UNUSABLE

So i rebuild the index using the command "alter index index name rebuild online command".

But after 2 or 3 days the same warning is re-occurring.

Can anybody explain why this is happening???

So 1 reason for index being unusable is that the respective segment was moved to some other location and hence the index cant be used.

The solution that is left is to drop and recreate it

But i want to know why this warning is reoccurring even after rebuilding the index

[Former Member · Accepted solution]

Unusable KE indexes are often related to the scenario described in SAP Note 1012607.

[Former Member · Accepted solution]

Problem solved

Thank you very much

SAP note 1042182 solved it also 1012607 helped

[former_member204746]

index is unusable because you are trying to recalc stats on this table while it is being /created/deleted/loaded by BW loads.

recalc your DB stats when there is not BW loading activity.

[Former Member]

Hi,

Correct me if am wrong

For B/W loading problem occurs for bitmap indexes as a range of row is locked,hence they are dropped and recreated!!

but i dont think is a problem for normal indexes as in this case

SQL> select INDEX_TYPE "TYPE",LAST_ANALYZED from dba_indexes where INDEX_NAME = '/BIC/B0000516000KE';

TYPE LAST_ANALYZED

-

---

-

---

NORMAL 23-AUG-09

So kindly explain!!

[lbreddemann]

> Correct me if am wrong

Ok - here we go

>

> For B/W loading problem occurs for bitmap indexes as a range of row is locked,hence they are dropped and recreated!!

Well the locking behaviour of bitmap indexes is one issue, yes. It can limit the paralllelity of actions on the indexed tables so you may want to get rid of them before doing parallel loads/updates.

More annoying than this is the problem that bitmap index maintenance is not as efficient as it is for b-tree indexes - bitmap indexes tend to grow very large when data is updated.

That's the primary reason for kicking them out before loading data.

> but i dont think is a problem for normal indexes as in this case

No it isn't. But nobody said it is.

> SQL> select INDEX_TYPE "TYPE",LAST_ANALYZED from dba_indexes where INDEX_NAME = '/BIC/B0000516000KE';

>

> TYPE LAST_ANALYZED

> -

---

-

---

> NORMAL 23-AUG-09

>

> So kindly explain!!

The issue here is: the index contains physical pointers to the table row locations.

If you perform any action that changes the physical location (e.g. alter table move, truncate table, drop/exchange partition...) then these pointers are not correct anymore and Oracle needs to invalidate them.

This is done by flagging the index as unusable.

So here you need to check what is done with the table - then you know when to rebuild the index.

regards,

Lars

[lbreddemann]

> So 1 reason for index being unusable is that the respective segment was moved to some other location and hence the index cant be used.

>

> The solution that is left is to drop and recreate it

>

> But i want to know why this warning is reoccurring even after rebuilding the index

Because after you rebuild the index, the table had been moved, truncated, ... again.

Since this is a BW table, it's likely that there was some kind of data load running and that the reponsible process chain did not take care of rebuilding the index again.

regards,

Lars