Do Oracle Indexes have to be dropped & rebuilt periodically?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Do Oracle Indexes have to be dropped & rebuilt periodically?

  1. #1
    Join Date
    Feb 2001
    Posts
    3
    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Wink

    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

  3. #3
    Join Date
    Mar 2001
    Posts
    5
    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.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  5. #5
    Join Date
    Feb 2001
    Posts
    3

    Oracle vs. dBase?

    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






  6. #6
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    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 :-)

  7. #7
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    hrishy,
    Well said (put).

  8. #8
    Join Date
    Feb 2001
    Posts
    295
    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.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

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