Analyze schema v/s Rebuild Indexes

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

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

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

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

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

Quote:

---

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.

---

I think you are right ocpwannabe.

what do the others have to say about it anyways.

Ronnie

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

Quote:

---

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

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.

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...

It is important that the indexes are rebuilt, and in the course, the index on the primary key is rebuilt.

But you don't rebuild the PKs themselves.

This is sort of a namegame.

Is it correct that whenever a Primary Key is created a corresponding Index is created.

Am I correct when i say this.

Yes, an index is created when a primary key is created or enabled.

-Ken

Index is created together with primary key only if appropriate index is not allready present in the system. If an index that covers PK constrained columns is allready there, PK uses this existing index to inforce the constraint. And in this case index does not need to be created as an unique index.

True.