INDEX THAT IS NOT USED
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: INDEX THAT IS NOT USED

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hi
    We are in process on restructuring/reorganizing the database and find out that DBA before has created many indexes on tables and lot of them are not needed and we want to drop them.

    How do we find index that are created on tables and yet are not used ?

    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    What version?

    In 8i, the standard response is "use catio.sql", although I have had marginal results with catio. You can also periodically sample v$object_usage, but this method is only a sampling and not a complete view of what is being used over time. In short, there's no "Easy" way to do it in 8i.

    In 9i, you can use index monitoring. See http://otn.oracle.com/docs/products/...8a.htm#2113145 for details.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Jeff
    Thank you for the prompt response. In that case can you tell me how to get the list of duplicate index or table
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Look at dba_ind_columns
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2002
    Posts
    219
    This brings to mind:
    "Don't ever take a fence down until you know the reason why it was put up."
    -- Gilbert Keith Chesterton (1874-1936)
    yodaDBA@hotmail.com

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