analyze or rebuild indexes

[midora]

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

[davey23uk]

spool it into a file and run the file

[gandolf989]

You should not make a habit of rebuilding indexes. It won't help performance, and may in the short run hurt performance.

[Mr.Hanky]

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.

[midora]

i was planning to run stat for the Tables and then rebuild the indexes, will it help to increace some perfomance?

Thanks

[Mr.Hanky]

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?

[LKBrwn_DBA]

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.

[davey23uk]

i was planning to run stat for the Tables and then rebuild the indexes, will it help to increace some perfomance?

Thanks

no

[LKBrwn_DBA]

Also you should check what Mr. Jonathan Lewis has to say in this dbazine.com article!