Analyze schema v/s Rebuild Indexes

[ronnie]

Hi,

If one analyzes the schema then is rebuilding the indexes necessary.

I mean analyze will take care of it anyways.

Please let me know if I am right or wrong.

Thanks
Ronnie

[julian]

After you analyze, there is no need to rebuild indexes.

On the other hand, after you rebuild indexes, you better analyze.

[ocpwannabe]

analyze has nothin to do with rebuild indxs. if ur index has holes before analyze, it'll still have holes after.

[ronnie]

I analyze my tables once a week every sunday night.

basically there is no activity on the database after that till monday morning.

I also have events scheduled in my OEM to alert me if any of the indexes need to be rebuild and I still get alerts immediatly after the whole schema has been anaysed.

why is that.

Ronnie

[ocpwannabe]

Originally posted by ocpwannabe
analyze has nothin to do with rebuild indxs. if ur index has holes before analyze, it'll still have holes after.

[ronnie]

I think you are right ocpwannabe.

what do the others have to say about it anyways.

Ronnie

[KenEwald]

Periodic reindexing will recover space (especially if you update the base table frequently). Updates force the index to create new leaf blocks, and the old ones just don't get recycled or deleted.

I've realized significant performance increases by rebuilding indexes. However, it's not something you want to do weekly. We do it once a year or as needed .. that seems to be enough.

Analyzing your table will provide more accurate statistics for the cost based optimizer.

-Ken

[ronnie]

Originally posted by KenEwald
Periodic reindexing will recover space (especially if you update the base table frequently). Updates force the index to create new leaf blocks, and the old ones just don't get recycled or deleted.

I've realized significant performance increases by rebuilding indexes. However, it's not something you want to do weekly. We do it once a year or as needed .. that seems to be enough.


Analyzing your table will provide more accurate statistics for the cost based optimizer.

-Ken

What exactly is the difference between analyzing and rebuilding indexes.

Dont both of them do the same job.

Ronnie

[marist89]

As KenEwald pointed out, rebuilding your indexes reshuffles your leaf nodes and removes any leaf nodes that are empty. I usually rebuild indexes after a large number of rows has been deleted or the height gets really large.

Analyze will compute statistics on your data to help the optimizer choose the best execution method. You should analyze your tables whenever the data distribution changes drastically.

And no, they don't do the same job.

[ronnie]

Do the Primary Keys also have to be rebuild like indexes as it is nothing but a unique index.

Please correct me if I am wrong...