Q

Remove fragmentation of tables and indexes in Oracle

How to remove fragmentation of tables and indexes in Oracle when tablespaces are in LMT and DMT?

Hi,
I am working as an Oracle DBA at MNC in India. My question is how to remove fragmentation of tables and indexes in Oracle when tablespaces are in LMT and DMT.

The best way to minimize tablespace fragmentation is to use Locally Managed Uniform Sized Tablespaces. With Oracle 10g, even your system tablespace can now be LMT. If you are not using Locally Managed Uniformed Sized Tablespaces, I highly recommend you start. This alone can go a long way to minimizing fragmentation.

That being said, what do you do if you have fragmented tables and indexes? The best way to remove fragmentation of a table or index is to re-create or rebuild it. The best way to accomplish this is to use the ALTER TABLE MOVE and ALTER INDEX REBUILD statements. If you are using DMT tablespaces, then first create new LMT tablespace with a uniformed size. Then use the following to move your tables and indexes to it.

  Alter Table Tab1 move tablespace NEW_LMT_TS;
  Alter Index Ind1 rebuild tablespace NEW_LMT_TS;

Even with LMT, indexes can sometimes become fragmented over time due to inserts and deletes of rows in the index leaf pages. I have found the following useful to identify fragmented indexes.

  1. Run an analyze index validate structure on your indexes.
  2. Query the index_stats table. If the percentage of deleted leaf rows to total leaf rows (DEL_LF_ROWS/LF_ROWS) is greater than 20%; then, the index probably should be rebuilt.
Hope this helps.
This was first published in May 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close