unusable indexes

[learn]

What does unusable index mean? Does that mean fragamented indexes?

[wren01]

it's not fragmented index. fragmented index still can be used. unusable index is the index not correctly corresponding to table, like some operation on partition table cause correspondin index unusable (index didn't changed, need rebuild).

[jdorlon]

One thing that comes to mind which can cause unusable indexes is when you move the table with an ALTER TABLE MOVE stmt.

[learn]

So, how can I check for unusable indexes? Can any body write/show me a script that does
that?

[wren01]

query dba_indexes view. there is a column: status.

[learn]

So to rebuild those unusable indexes, do you just
alter index index_name rebuild
just like you do when the index is fragmented? How do you solve the problem when you realize that your index is unsable?

thanks,
learn

[wren01]

yes, rebuild it. you can even rebuild index to another tablespace to move index.

[learn]

after you query the the status from dba_indexes and the index is unusable, what does the status column display?
Does it say not valid or unusable?

[dorothy]

that a good a question. Mine always shows valid so I guess it means your indexes are good. If they are unusable, I don't know how it will show

[sreddy]

shows UNUSABLE

[url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76961/ch232.htm#877[/url]