Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 7 November 2013

Use of ‘SHRINK’ command to deal fragmented Table.

When the table are fragmented, queries on those table will automatically get slow due to scanning many more blocks which has no data on it. To remove fragmentation from table (in 10g onwards) you can use Shrink and DBMS_REDIFINITION method. Check this post to handle fragmentation before version oracle 10g: How to Remove Fragmentation.
If the fragmentation table is small in size it is better to use ‘SHRINK’ command else use ‘DBMS_REDEFINITION’.
Consider the example table having fragmentation up to 83%
OWNER    SEGMENT_NAME      SEGMENT_TYPE TBS_NAME  MBs  WASTED_MB Wasted % 
-------- ----------------  ------------ --------- ---- --------  ------- 
HRMS    PAY_PAYMENT_MASTER TABLE        HRMS      344   284        83
When we run any query on this table it has to scan all the 344 MB data, which is in fact time consuming task and will increase execution time for queries. As this not very big table we can use ‘shrink’ command to remove the fragmentation.
Step1: Check row movement is enabled or not for the table PAY_PAYMENT_MASTER. If not we need to enable it.
SQL> select row_movement from dba_tables where table_name='PAY_PAYMENT_MASTER' and owner='HRMS'; 
ROW_MOVE 
-------- 
DISABLED 
SQL> alter table HRMS.PAY_PAYMENT_MASTER enable row movement; 
Table altered.
SQL> alter table HRMS.PAY_PAYMENT_MASTER shrink space compact
Table altered.
The above command will re-arrange used and empty block but the High Water Mark of this table (HWM) still remains the same. Thus we need to reset the HWM for this table. Step2: Reset the high water mark
SQL> alter table HRMS.PAY_PAYMENT_MASTER shrink space; 
Table altered.   
Step3: Check and Rebuild Indexes associated with this table.
SQL>Select index_name,index_type from dba_indexes where table_name='PAY_PAYMENT_MASTER'; 
INDEX_NAME              INDEX_TYPE 
----------------------- --------------------------- 
PAY_PAYMENT_MASTER_PK   NORMAL 
SQL> alter index HRMS.PAY_PAYMENT_MASTER rebuild online;
Index altered.
Now gather statistics on this table and finally check the table details again. You will see the table size drastically reduced.
SQL> EXEC DBMS_STATS.gather_table_stats('HRMS', 'PAY_PAYMENT_MASTER', estimate_percent => 55, cascade => TRUE); 
PL/SQL procedure successfully completed.     
OWNER    SEGMENT_NAME        SEGMENT_TYPE TBS_NAME  MBs  WASTED_MB Wasted% 
-------- ------------------- ------------ --------- ---- --------  ------- 
HRMS    PAY_PAYMENT_MASTER   TABLE        HRMS      58   8        14
From the above example you can see the table size get reduced from 344 to 58 MB (waste only 14%). Now if you try queries against this table will definitely faster than before.

0 comments:

Post a Comment