This topic seems to be some kind of „Evergreen“, since it comes up regularly in my courses and also in the OTN Discussion Forum. I decided therefore to cover it briefly here in order to be able to point to this post in the future.
Short answer: Probably not
If the intention of the reorganizing operation is to gain space resp. „defragment“ the table, this operation is very likely just a waste of effort & resources. I will try to illustrate my point with a simple demonstration that anybody with access to an Oracle Database can easily reproduce. My demo Database is 11gR2, but the same can be done with 10g also. If you are on an even older version, there is no SHRINK SPACE available, so you would have to use MOVE instead. I prepare a little demo Table with 1Mio rows now – the time_id population was the hardest part of that demo for me 🙂
SQL> create table sales as select 'Oracle Enterprise Edition' as product, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id , 5000 as amount_sold, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy') as time_id from dual connect by level<=1e6; Table created. SQL> select time_id ,count(*) from sales group by time_id order by 1; TIME_ID COUNT(*) --------- ---------- 01-JAN-10 83333 01-FEB-10 83334 01-MAR-10 83334 01-APR-10 83334 01-MAY-10 83334 01-JUN-10 83333 01-JUL-10 83333 01-AUG-10 83333 01-SEP-10 83333 01-OCT-10 83333 01-NOV-10 83333 01-DEC-10 83333 12 rows selected.
SQL> select segment_name,bytes/1024/1024 as mb from user_segments;
The table contains about 83000 rows per month. Now I will delete the first quarter of rows:
SQL> delete from sales where time_id<to_date('01.04.2010','dd.mm.yyyy'); 250001 rows deleted. SQL> commit; Commit complete.
SQL> select segment_name,bytes/1024/1024 as mb from user_segments; SEGMENT_NAME MB -------------------- ---------- SALES 54
This is the starting point of a possible reorganization: Although 250k rows got deleted, the table consumes the same space as before. In other words: The High Water Mark did not move. A reorganization would move the High Water Mark and would regain the space that was consumed by the 250k rows, like shown in the below picture:
The question is: Is that necessary? If inserts would take place after the deletion again, then the space would get reused without any need to reorganize:
SQL> insert into sales select rownum as id, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id , 5000 as amount_sold, to_date ('01.' || lpad(to_char(mod(rownum,3)+1),2,'0') || '.2011' ,'dd.mm.yyyy') as time_id from dual connect by level<=2.5e5; 250000 rows created. SQL> commit; Commit complete. SQL> select time_id ,count(*) from sales group by time_id order by 1; TIME_ID COUNT(*) --------- ---------- 01-APR-10 83334 01-MAY-10 83334 01-JUN-10 83333 01-JUL-10 83333 01-AUG-10 83333 01-SEP-10 83333 01-OCT-10 83333 01-NOV-10 83333 01-DEC-10 83333 01-JAN-11 83333 01-FEB-11 83334 01-MAR-11 83333 12 rows selected.
I inserted a new quarter of rows. The table remains in the same size as before:
SQL> select segment_name,bytes/1024/1024 as mb from user_segments; SEGMENT_NAME MB -------------------- ---------- SALES 54
That is exactly the point I like to emphasize: If you have inserts following deletes, reorganization of tables is not necessary! Only if that is not the case (Table gets no longer inserts after deletion), you may reorganize it:
SQL> delete from sales where time_id<to_date('01.07.2010','dd.mm.yyyy'); 250001 rows deleted. SQL > commit; Commit complete. SQL> alter table sales enable row movement; Table altered.
SQL> alter table sales shrink space; Table altered.
SQL> select segment_name,bytes/1024/1024 as mb from user_segments; SEGMENT_NAME MB -------------------- ---------- SALES 40.25
The space consumed by the table got reduced now and is usable for other segments. Although the table is not locked during the SHRINK SPACE operation and users can do DML on the table as usual, the operation is not „for free“ in terms of resource consumption. It does a lot of I/O, creates a lot before images that consume space in the UNDO tablespace and the operation modifies many blocks, so lots of redo protocol (and therefore many archived logs, probably) gets generated.
If you really think that you need that kind of reorganization regularly, you should probably evaluate the Partitioning Option here:
SQL> create table sales_part (product char(25), channel_id number, cust_id number, amount_sold number, time_id date) partition by range (time_id) interval (numtoyminterval(3,'month')) (partition p1 values less than (to_date('01.04.2010','dd.mm.yyyy'))) ;
Above command created a table that is partitioned by the quarter. We could simply drop a partition instead of deleting lots of rows and we would never need to reorganize to regain space here. If you are on 10g, the feature INTERVAL PARTITIONING is not available there. You can then use RANGE PARTITIONING with the additional effort that you need to create the necessary range partitions manually.
Conclusion: Before you decide to reorganize a table, make sure that this is really necessary, because likely it isn’t 🙂
Watch me explaining the above on YouTube:
#1 von Gary am August 24, 2010 - 03:02
For some reason (possibly the mention of forums), the automatically generated „possibly related posts“ is currently the evocatively titled „Boobs are like spaceships“.
#2 von Uwe Hesse am August 24, 2010 - 11:15
thanks for the comment. I am not responsible for the automatically generated links to other wordpress posts below my postings. It’s a wordpress thing. Apologies to everyone who may feel aggrieved by these links.
#3 von coskan am August 28, 2010 - 10:52
I think for oracle it is again it depends 🙂
What if application uses direct path insert?
#4 von symield am August 28, 2010 - 14:54
Coskan, that’s way „direct path insert“ works.
Uwe Hesse, sometimes we want to reduce HWM for performance purpose. Oracle database will scan all the blocks under HWM for some kinds of access path.
Can you share some points about how to reduce HWM without affecting application most? What’s the best way to do so?
#5 von jametong am August 30, 2010 - 15:47
move or shrink may be the only option to lower the high water mark
#6 von Uwe Hesse am August 31, 2010 - 08:53
you are right: Direct loads will not reuse empty space before the High Water Mark. So this could be a valid reason why you want to reorganize tables even if subsequent inserts will happen.
the best way to move the HWM without affecting applications is SHRINK SPACE – it allows DML while the operations is running. It will increase the overall I/O load of the system for the time running, though, will consume space in the UNDO tablespace and will lead to the creation of more archivelogs than usual.
you are right. Let me add that DBMS_REDEFINITION can also be used for that purpose, although the main reason for using it would be the desire to change the structure of a table, not only to reorganize it to regain space.
#7 von Mark Bobak am September 4, 2010 - 14:31
Nice little demo, Uwe.
Clear, and to the point.
#8 von Uwe Hesse am September 5, 2010 - 09:06
Thank you for the nice feedback, Mark! That is exactly what I strive to achieve: Being clear and to the point 🙂
#9 von John Seaman am September 16, 2010 - 09:44
Great article Uwe. It is always important to consider why we are doing something. Then we can decide whether or not the cost is worthwhile. And in this case you’ve shown that it may well not be worthwhile.
#10 von Uwe Hesse am September 20, 2010 - 09:21
Thanks for your positive feedback, John 🙂
#11 von Kavita am Juni 8, 2012 - 18:44
Sir u have explained it very easy way with nice examples ………from this article we can conclude there are commands but when they should be used in what situations …….thanks a ton
#12 von vinayak am August 9, 2012 - 13:09
good explian… one question After the shrink space the table may goe in to full table scan then index not be used in that case That means the will performance issue ? Please clear my point and help me to resolve the doubt
#13 von Uwe Hesse am August 9, 2012 - 13:44
Kavita, you’re welcome – sorry for the late reply 🙂
Vinayak, the shrink will lead to different Optimizer Statistics than before. This may or may not lead to changed execution plans. That is true and can be a concern, but that is a very common thing not only related to reorganizing. Consider to use SQL Plan Baselines when you want Plan Stability. I have a posting about them here: https://uhesse.com/2009/05/19/restrict-the-usage-of-new-execution-plans-with-sql-plan-baselines/
#14 von Andy am Februar 13, 2013 - 16:03
Thank you for this explanation. I am running a 10.2.0.4 Database and i reorganize offline with „move table“ every half year. I might try this shrink space option and do it online instead!
But i have 2 questions. What is if my Table is 50gb big and my Undo ist only 10gb. How can i determine a good size for an Undo Tablespace? I have much tables with Lob Segments. Doe’s „alter table shrink space cascade“ work good as well on big (100gb) Tables?
#15 von Wolfgang am März 12, 2013 - 17:08
„Conclusion: Before you decide to reorganize a table, make sure that this is really necessary, because likely it isn’t.“
This is based on the assumption that „something“ fills up the gaps which were deleted. But what if this is wrong. Especially with partitioning you finally have to reclaim space. In my experience then the DBA deletes parts of data because some are „more important“ or some users pay for longer availability while others don’t. At the end of disk space you have to do something to reclaim space.
#16 von Uwe Hesse am März 14, 2013 - 16:50
Wolfgang, it is of course sometimes useful to use shrink space respectively move. But probably not in the majority of cases. I do not see the requirement to use it especially in relation to partitioning. Just drop partitions instead of delete rows – no need to reorganize here.
#17 von sumanth am März 20, 2013 - 09:51
what is the mail diff between MOVE and shrink . what is the need of shrink from 10g.
#18 von Uwe Hesse am März 20, 2013 - 15:25
Sumanth, the major difference between SHRINK SPACE (10g New Feature) and MOVE (8i New Feature) is that MOVE locks the table during the process, preventing any DML. SHRINK SPACE doesn’t; it’s an online operation. MOVE can do more than just reorganize, on the other hand, e.g. indeed move the table into another tablespace.
#19 von sumanth am März 21, 2013 - 05:05
thank you , so releasing the extents,space and reset the HMV, all these will act same for both the operation.
#20 von Uwe Hesse am März 21, 2013 - 08:50
Not necessarily exactly the same. SHRINK SPACE will e.g. not resolve every row migration. Let me put it this way: If your goal is just to reorganize, SHRINK SPACE is the way to go. MOVE is more powerful than SHRINK, but locks the segment against DML – in the present version (8i – 11g)
#21 von sumanth am März 22, 2013 - 06:09
#22 von Uwe Hesse am April 2, 2013 - 07:43
You’re welcome, sumanth 🙂
#23 von Michael am Juni 18, 2013 - 14:13
Hello Uwe, reorganization is usefull and necessary if the table will be selected by FTS(FullTableScans).
Each FTS is running from the beginning (first) block to the end (hwm = last) block.
Sure, nobody likes FTS, but in data warehouses this can be….
#24 von Uwe Hesse am Juli 9, 2013 - 20:08
Michael, that may be the case – did you measure the effect your reorg had? Got a reduction of DB Time?
#25 von vaibhav am Oktober 25, 2013 - 14:02
In the case of index how its beneficial ?
For example : alter index TABTRANS_TXN_DATE shrink space
Kindly clarify with example…..thanks in advance
#26 von Pari am November 3, 2013 - 12:08
Thank you Uwe for such a clear explanation.claps for u!!!!!!
i have a doubt when we use deallocate unused for reclaiming the space even if we have shrink space to reclaim it?
#27 von Uwe Hesse am November 8, 2013 - 12:07
Pari, you don’t need the command deallocate unused after a shrink space operation. The free space is released automatically.
#28 von Vab am September 30, 2014 - 05:30
#29 von ishar am Dezember 4, 2014 - 06:56
In one of the comments you mentioned Move is more powerful than shrink, can you please elaborate on this point? why move is better?
#30 von Uwe Hesse am Dezember 4, 2014 - 18:29
ishar, SHRINK can „only“ gain space, whereas MOVE can also modify storage attributes like PCTFREE or change the tablespace or compress/uncompress the table.
#31 von Mark am Februar 10, 2015 - 00:17
Had a 15 million row table for which online transaction performance was ok but a batch job that read every row was taking about 2 days to finish. I reorged the table via move and the same batch job completed in 12 minutes. The space used did not change much. Online transaction performance did not improve much but anything that read all or most of the rows improved dramatically. Unfortunately, I did not check before hand. Is this something most likely caused by row chaining?
#32 von Gerard Alexander am Juli 15, 2015 - 17:16
Someone who at least thinks logically as opposed to creating extra work. This is what I did for years and it worked fine. With partitions different, but it depends on the situation at hand. Cannot comment on the 15 million row aspect, excOLTP ept that I try to capture deltas to process for batch; that makes a big differences as well. OLTP can re-use space generally imho.
#33 von Sanjay am Januar 30, 2016 - 18:17
We have both partitioned and non-partitioned tables, so can i go with the below steps in 11gR2,
SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;
This enables row movement of the table. THis is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.
SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;
This shrinks the used space of the table, but does not update the HWM. This is usefull if you don’t want to have a table-level lock during business hours.
SQL> ALTER TABLE MYTABLE SHRINK SPACE;
This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with ‚compact‘ option has been done, just to update the HWM.
SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE;
This command shrinks the contents of the table and all dependent objects like indexes.
#34 von kouss am Oktober 20, 2016 - 23:25
Hi Uwe ,
We have tables that are according to the segment_advisor fragmented up to 600mB daily . I wanted to know what is the impact on the tables‘ statistics (same question regarding indexes shrink) after an enable row movement +shrink. I created a job which does that but does it require a stat calculation along with thw operation?
thank you a lot for the insightful post
#35 von Jeyalin Sheeju am Juli 14, 2017 - 12:40
Thanks a lot for this post… A good read 🙂
#36 von Kushal Puri am Dezember 11, 2017 - 06:28
We have tables that will have 1million records.So I have to delete those records after a month based on some condition.So do I need to reorganize the tables or rebuild the indexes for better read operations and avoid fragmentation? For your knowledge, every month 1million records will be created and deleted. Any help would be appreciated.