This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 15, 2012 4:14 AM by JaseerV RSS

Index recreation

JaseerV Newbie
Currently Being Moderated
Hi,

What is the benefit of index recreation?

How to recreate indexes ?
  • 1. Re: Index recreation
    KeithJamieson Expert
    Currently Being Moderated
    in order to create indexes you have to drop the index first and then reissue the create index statement.

    In the vast majority of cases index re-creation on oracle is a waste of time.

    Edited by: Keith Jamieson on Oct 11, 2012 1:59 PM
    changed creation to recreation
  • 2. Re: Index recreation
    Sven W. Guru
    Currently Being Moderated
    Jaseer V  wrote:
    Hi,

    What is the benefit of index recreation?
    Almost none. Sometimes the index is faster after a rebuild or after a drop/create than before. Often not.

    imo: The main point is that the DBAs think they did something useful. So they feel needed after they re-created an index.

    >
    How to recreate indexes ?
    Drop index ...
    Create index ...

    A very educational read is that old thread from Tom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2290062993260

    Edited by: Sven W. on Oct 11, 2012 3:01 PM
  • 3. Re: Index recreation
    phaeus Pro
    Currently Being Moderated
    Hello,
    a index can be recreated with alter index ... rebuild or with drop and create for example. It depends on your need.

    The benefit for recreate a index can be
    -reduce fragmentation
    -release unused space
    -or to optimize him like to make it reverse

    hope this helps
    regards
    Peter
  • 4. Re: Index recreation
    Paul Horth Expert
    Currently Being Moderated
    In most cases there is no benefit:

    see http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4110993648759
  • 5. Re: Index recreation
    SomeoneElse Guru
    Currently Being Moderated
    Don't drop an index unless you have an extremely good reason to do so.

    Dropping an index may invalidate SQL in the cache. Any execution plan that used the index is now no good. SQL statements will have to be re parsed.
  • 6. Re: Index recreation
    JaseerV Newbie
    Currently Being Moderated
    Hi,

    I have recreated indexes . now I am facing the performance issue on the table!!

    The query is taking too much time to execute!!

    Any Idea how to increase the performance after recreating index?
  • 7. Re: Index recreation
    phaeus Pro
    Currently Being Moderated
    Hello,
    in which way did you recreate your index?
    If you drop it und create it (instead of rebuild it) you are maybe missing your statistics. In this case you need a analyze index command or dbms_stats.

    Give us some description what you have doing ?

    regards
    Peter
  • 8. Re: Index recreation
    JaseerV Newbie
    Currently Being Moderated
    Hi Peter,

    I have droped and recreated 8 indexes on one table.

    Now the select query with some filter condition on that table is taking too much time to execute. Previously the same query was executing fast.

    Could you please let me know how to increase the perfomace without changing the SQl query as I don't have no DBA knowledge :)
  • 9. Re: Index recreation
    phaeus Pro
    Currently Being Moderated
    Hello,
    my best tip for you is that you did not have any optimizer statistics for this indexes anymore.
    Here is the documentation
    http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_4005.htm

    You can check the dba_index view (column last_anaylzed) if you have statistics. In case you are missing this statistics the optimizer did not take your indexes into account.
    Use alter index <owner>.<index_name> compute statistics.

    Also (be aware that the database do a time long more parses) you can after computing statistics a alter system flush shared_pool; to generate new execution plans for your statements.

    regards
    Peter
  • 10. Re: Index recreation
    SomeoneElse Guru
    Currently Being Moderated
    I have droped and recreated 8 indexes on one table.
    Why?
  • 11. Re: Index recreation
    sb92075 Guru
    Currently Being Moderated
    post complete results from query below

    SELECT * FROM V$VERSION;
  • 12. Re: Index recreation
    JaseerV Newbie
    Currently Being Moderated
    to claim the unused spaces
  • 13. Re: Index recreation
    JaseerV Newbie
    Currently Being Moderated
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

    Edited by: Jaseer V on Oct 13, 2012 9:33 PM
  • 14. Re: Index recreation
    sb92075 Guru
    Currently Being Moderated
    Jaseer V  wrote:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

    Edited by: Jaseer V on Oct 13, 2012 9:33 PM
    I am saddened to see that you are unwilling or incapable to follow simple directions.

    I won't waste more of my time trying to assist you.

    You're On Your Own (YOYO)!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points