unusable indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: unusable indexes

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    169

    Thumbs up

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

  2. #2
    Join Date
    Jan 2001
    Posts
    71
    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).

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    One thing that comes to mind which can cause unusable indexes is when you move the table with an ALTER TABLE MOVE stmt.

  4. #4
    Join Date
    Nov 2000
    Posts
    169
    So, how can I check for unusable indexes? Can any body write/show me a script that does
    that?

  5. #5
    Join Date
    Jan 2001
    Posts
    71
    query dba_indexes view. there is a column: status.

  6. #6
    Join Date
    Nov 2000
    Posts
    169
    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

  7. #7
    Join Date
    Jan 2001
    Posts
    71
    yes, rebuild it. you can even rebuild index to another tablespace to move index.

  8. #8
    Join Date
    Nov 2000
    Posts
    169
    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?



  9. #9
    Join Date
    Oct 2000
    Posts
    90
    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

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    shows UNUSABLE

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width