Drop index and recreate

Posted on 2006-06-06
Last Modified: 2012-05-05
Hi Team,

I have a question regarding indexes.

Why do we required to drop index on prodcution database and recreate it  again.  I have seen somany times, guys are talking about droping index and recreate.

Can any one explain, for what reason we need to drop the existing index and recreate it. Suppose i have table 10milion records and those record occupied 1 million of data blocks. Means 10 records are there in one block. Here i have create index on that table(10million records), how can we know howmany leaf blocks used for this index. Somany question here again :(

1) Actually what is leaf block and leaf entry.

Thanks & Regards,
Question by:bhsrao
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    leaf block is the block that is the leaf on the index (b-) tree containing the index entries. the non-leaf blocks only contain internal structure information for the index data. a leaf entry is 1 index value with the lookup information to the actual rows.

    recreating indexes by first dropping them + creating new index is only needed on those data systems that do not allow online index rebuild.

    rebuilding indexes is only needed on tables that do not have a non-heap structure. in sql server, as soon as you have a clustered index, you don't need to rebuild the indexes of that table at all (unless corruption of the index).

    corruption is actually the only reason why nowadays one should recreate a index, except in oracle where you want to change the storage clauses of the index which cannot be done without recreating the index completely
    LVL 3

    Assisted Solution

    If you have a 10 million row table and insert 10 new rows then you don't drop index. You simply insert new records.

    But if you have a datawarehouse and you have to delete 1 million of historical rows then bulk load 1 million of new rows, then, belive me, it is much faster to drop these indexes, load data and re-create the indexes. You avoid 2 millions of index maintenance operations.

    And 10 millions rows is not much in datawarehouses...

    LVL 4

    Accepted Solution

    Indexes are dynamically updated when INSERT, UPDATE, or DELETE operations are performed on a table. To minimize overhead during large inserts, updates, or deletes, you can delete indexes with large keys and recreate them after the changes are made. This depends, of course, on table size versus insert, update, or delete size. For example, if you have one million rows in the table and you are updating one hundred of them, deleting the indexes will cause more overhead than leaving them intact.

    As rows are inserted into a table, the indexes expand in number of pages and in number of levels. As rows are updated or deleted, the number of index pages may decrease, but the number of levels remains the same. During deletes, the number of occupied index pages will decrease as pages are emptied, but those pages not completely emptied will be sparsely populated causing an unnecessarily high number of index levels. For performance reasons and storage efficiency, you should drop and recreate indexes after multiple updates and deletes to a table.

    Temporary indexes can be used by applications to improve performance. Applications that run on a periodic basis (once a month, for example) can create indexes to use while they are running and drop the indexes before terminating. (The DDL Enabled flag in the DBECon file must be set to YES in order to create and drop temporary indexes.) This way application data access is optimized at run time and overhead is minimized for updates when the application is not being run.

    In all cases, you need to weigh the size of the table against the amount of work that is required to drop and recreate the index. In general, if your tables are extremely large, you should only drop and create indexes if you are experiencing poor performance or if you need to load a large amount of data. For smaller tables, you may want to recreate indexes periodically to ensure optimal performance.

    hope this will answer your query.

    Chetan Sachdeva

    Author Comment

    Hi AnagelIII,

    I am using oracle.
    So, if want to increase index storage, fist i have drop index and recreate index with large storage option. Pleae correct me if wrong.

    You said, "Rebuilding indexes is only needed on tables that do not have a non-heap structure". What is non-heap structure, how to find my table is having heap-structure.

    Thanks & Regards,
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >So, if want to increase index storage, fist i have drop index and recreate index with large storage option. Pleae correct me if wrong.
    well, if you want to change the initial/next extent size, and settings like inittrans and maxtrans.

    a non-heap structure is a index-organized table.
    note that those, in oracle, are rather avoided, giving some other problems (as it looks like), hence I use them rather in small lookup tables.

    In your case, with 10M rows, you might consider table partitionning, while csachdeva posted very useful information about when to consider index recreates.
    Index recreates should not be automatically done (ie regular schedule), but can be part of a loading script which loads tons of rows into the table.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now