Do Oracle Indexes have to be dropped & rebuilt periodically?

Hi,
I am having a problem like this. An Oracle table can have many indexes created manually. My question is whether these indexes have to be dropped & rebuilt periodically like in dBase. I have heard that if there are many deletions happening frequently to the table then you might have to drop and re-create the indexes. But I have read in the Oracle Manuals that Oracle will automatically take care of the indexes without the users ever having to do anything.
Is there any truth to this statement? Your advice would be greatly appreciated.

Regards,

of2

oracle can take care of the indexes by itself in most cases but the problem is with the cost based optimizer which oracle uses to build query execution plans after a delete operation these index structure ( b* tree )would get out of sequence and hence if you do not rebuild the index and dont anakyze your indexes after a massive delete operation then your queries would run slowly.

By the way there is difference between dbase and oracle although they are databses in the same way you cant compare a lizard and a dinasours although both belong to the reptile family right

hope thsi helps

It depends on a number of things, but mostly your application.

If your data is pretty volatile -- you have a huge number of update-inserts-deletes -- then it is sometimes worthwhile rebuilding indexes because they may become in-balanced.

However, unless you are seeing performance issues, or regularly delete-insert vast quantities of data I would say no, leave it alone.

So dont rebuild indexes unless you feel you have a problem. If you do have an issue and have to do this, then maybe make use of the 'alter index rebuild ' syntax to do it without interrupting service. Because remember - if you drop a primary index with users online, you run the risk of getting duplicates in the table.

Hope this helps.

Adam.

It is a good idea to rebuild the indexes periodically.
You should understand what a lopsided index is. Over a period of time, the index may grow on one side. And more over, when rows are dropped from the table, the corresponding holes in the index data block may not be used again. That is why, periodic reindexing is necessary in Oracle.

Reply to hrishy
------------------

Hi,

Thanks for you reply (All others as well). I think I got the answer to my query. But I think I am a little curios about the statment you made. i.e.

"By the way there is difference between dbase and oracle although they are databses in the same way you cant compare a lizard and a dinasours although both belong to the reptile family right "

OK, I agree the difference between Oracle and dBase/Clipper is tremendous. But I am of the firm opinion that for a small to medium scale application, where unauthorized access is not that of a problem it would be cost effective to go with dBase/Clipper than Oracle/Developer. I have been a dBase/Clipper programmer for 4 years before going Oracle. I know the cost difference between an dBase/Clipper HR package and an Oracle one is so great but when you really compare the 2, the funtionality and access times etc. there is not much of a difference. In fact you might not believe that dBase/Clipper application is much faster that the Oracle Developer one. Especally when it comes to reporting. Also dBase too can handle over a million records of data. So all in all I still believe that for small to medium scale applications dBase/Clipper is much better (i.e. cost-effective) than Oracle/Developer.
What is your opinion?

Thanks & Regards,
OraFan2

hi orafan

different strokes for different folks

yeah agreed with what you have said just now .its like we are going on a picnic and we have to climb the hill proably we could take a some sandwiches a tent and a camera (to shoot our pics :-) ) and smoe coke that would be sufficient .imagine carrying the same gear and attempting to climb the mount everest (i gotta be kiding) by the same token if we want to use ant transaction intensive application then dbase would be to its knees (i know yor application code code the transaction logic but that would mean repaeting the logic in each nad every application visa vis centralised transaction processing ACID properties and .....)

so you c its matter of climing the mount everest or the hill

so enjoy the picnic :-)

hrishy,
Well said (put).

According to Oracle 8i Concepts manual:

---
The B-tree structure has the following advantages:
(...)
* B-tree indexes automatically stay balanced.
(...)
---

So, why the need of rebuilding indexes periodically? Of course auto-balancing may not be true for all index types, but B-Tree is by far the most common, am I right?

And... does anyone know how Oracle manages that feature? What processes are involved in auto-balancing indexes?


Adriano.

When the PK column gets data like 1,2,3 ....(Sequence Number), then the data is said to be lopsided (one side growth). This will create many branches in the structure. Consquently, Oracle needs many reads (potentially causing many Disk I/Os) to get a key and its related row id. That is why, periodic reindexing is required.