SQL> alter index SPSDD.ODM_SUPPLY_DEMAND_QUERY_I1 rebuild online SQL> select index_name,index_type from dba_indexes where table_name='KDM_SUPPLY_DEMAND_QUERY' Now we need to rebuild indexes for this table if any. Shrink space command will get complete in a short period of time for smaller tables.Īt this stage HWM will get reset. SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY shrink space As a best practice while running this command parallely check any blocking on this session if any blocking occurred for a period of time then it should be terminated. Be very caution in this stage because if the table being reorganized is heavily used (i.e concurrent DML activities) then Locks will be occurred which will impact the DB (if its in Prod)s. Now, we just need to reset the HWM of this table. At this time High Water Mark of this table still remains the same. SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY shrink space compact Ībove command does segregating the data and empty blocks.
Now we can rearrange used and empty blocks using below command. SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY enable row movement SQL> select row_movement from dba_tables where table_name='KDM_SUPPLY_DEMAND_QUERY' and owner='SPSDD' The first step is to check whether row movement is enabled on the table which is going to reorganized, if not we need to enable it, like below Since this is a samall table we can use SHRINK command to eleminate the fragmentation on the table. When we run any querys against this table it has to scan all the (both data & emmpty) blocks on the table i.e the whole 290M which is time consuming task, which leads to increase in execution time on the queries. SPSDD KDM_SUPPLY_DEMAND_QUERY TABLE SPSDDDAT 290 258 90Ībove output says KDM_SUPPLY_DEMAND_QUERY table sized 290M out of which 258M got wasted!
OWNER SEGMENT_NAME SEGMENT_TYPE TBS_NAME MBs WASTED_MB Wasted % In this post we are going to discuss using the SHRINK method.Ĭonsider the below table it had a huge fragmentation upto 90%! If the fragmented table is small in size we can go for the first option else the second option would be perfect. There are two ways to eliminate table fragmentation on a table. When the table are fragmented, queries on those tables will automatically get slow due to scanning many blocks which has no data on it! so how do we eliminate the fragmentation. Usually in a OLTP environment, tables are often get fragmented due to many DML activities on the table. In this post I am going to explain the easiest method to eliminate the fragmentation on the table.
0 kommentar(er)
