Table Fragmentation in Oracle

What is Fragmentation :-

 When there are heavy transactions such as deletes and updates are running in a table, Fragmentation comes in picture. Oracle will read up to high water mark (HWM) Which is nothing but
 'Point at which Oracle Consumed the Most Extents'
This is the reason  users will face slowness in the application to retrieve the data,because oracle not reset the HWM after deletions.


High Water Mark : Is  a pointer where oracle stops writing in the blocks and put a mark where it ends. OR  we can say its an Marker between Used and Unused Blocks.

 Example :-
Assume that you have 5 lac rows in your table which has accommodated in 1000 blocks, when you are executing SELECT query its taking 20 minutes to give the output, and If you deleted 4 lac rows from this table. and run again same SELECT query it will take same 20 minutes to read again. Here oracle read 1000 blocks again This is because of HWM is not reset by oracle after deletion.


We Can Reset HWM By Using These Methods.

1.  Alter Table Move.

2. Expdp / Impdp

3. Shrink Command.

4. CTAS

5. DBMS Redefinition Package.

                                                               ***************


>> You can check this link how to reset the HWM  and Remove Fragmentation from a table.


http://moindba.blogspot.com/2016/06/how-to-reorganize-table-using.html



 Happy Learning...