analyze or rebuild indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: analyze or rebuild indexes

  1. #1
    Join Date
    Aug 2008
    Posts
    118

    analyze or rebuild indexes

    Hi there,

    Is there a way to rebuild all index!

    I don't want to do one at a time:

    Alter index rebuild;


    or this:

    SELECT 'alter index '|| owner||'.'||index_name||' rebuild;'
    FROM all_indexes;


    Please guide me!!

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    spool it into a file and run the file

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    You should not make a habit of rebuilding indexes. It won't help performance, and may in the short run hurt performance.
    this space intentionally left blank

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    Well if you are going to rebuild all of them you can split the file into multiple files and run a few session simultaneously.
    You could also use the PARALLEL clause to try and speed it up as well as an analyze statement to avoid having to run stats after the rebuild.
    I remember when this place was cool.

  5. #5
    Join Date
    Aug 2008
    Posts
    118
    i was planning to run stat for the Tables and then rebuild the indexes, will it help to increace some perfomance?

    Thanks

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    Rebuild first, then generate stats for the tables and indexes.

    "Will it increase performance?" Well, that depends, you may want to try generating fresh stats first.
    Is it 10G?
    I remember when this place was cool.

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool bde_rebuild.sql

    1) Check Metalink Note:182699.1

    2) Rebuild or Coalesce?

    3) Compare costs

    4) Taking an export of the owning schema or database, specifying ROWS=NO so that data within the database is not exported, and then using the import with the option INDEXFILE will produce a file listing all of the commands to build the indexes.


    PS: You can use the "COMPUTE STATISTICS" option on the CREATE or ALTER INDEX commands.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by midora
    i was planning to run stat for the Tables and then rebuild the indexes, will it help to increace some perfomance?

    Thanks
    no

  9. #9
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Talking dbazine.com

    Also you should check what Mr. Jonathan Lewis has to say in this dbazine.com article!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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