Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6588
  • Last Modified:

Drop index and recreate

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,
3 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
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...

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
bhsraoAuthor Commented:
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,
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now