Reorganizing Tables in Oracle – is it worth the effort?

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:

SQL> select * from v$version;
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

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;
SEGMENT_NAME                 MB
-------------------- ----------
SALES                        54

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:

Picture of a table before and after reorganize

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;  2    3

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 archivelogs, probably) gets generated.

Reorganizing tables in #Oracle is most likely not necessary. <<Tweet

Reorganizing tables in #Oracle is NOT for free in terms of resource consumption. <<Tweet

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 :-)

About these ads

, ,

  1. #1 by Gary on 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. #2 by Uwe Hesse on August 24, 2010 - 11:15

    Gary,
    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. #3 by coskan on August 28, 2010 - 10:52

    I think for oracle it is again it depends :)

    What if application uses direct path insert?

    R@ORACOS> delete from sales where time_id commit;
    
    Commit complete.
    
    HR@ORACOS> select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='SALES';
    
    SEGMENT_NAME                                                                              MB
    --------------------------------------------------------------------------------- ----------
    SALES                                                                                     54
    
    insert /*+ APPEND*/ 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
      2    3    4    5    6    7    8    9   10  from dual connect by level select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='SALES';
    
    SEGMENT_NAME                                                                              MB
    --------------------------------------------------------------------------------- ----------
    SALES                                                                                     72
    
    
  4. #4 by symield on 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. #5 by jametong on August 30, 2010 - 15:47

    move or shrink may be the only option to lower the high water mark

  6. #6 by Uwe Hesse on August 31, 2010 - 08:53

    Coskan,
    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.
    Symield,
    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.
    Jametong,
    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. #7 by Mark Bobak on September 4, 2010 - 14:31

    Nice little demo, Uwe.

    Clear, and to the point.

    -Mark

  8. #8 by Uwe Hesse on 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. #9 by John Seaman on 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. #10 by Uwe Hesse on September 20, 2010 - 09:21

    Thanks for your positive feedback, John :-)

  11. #11 by Kavita on June 8, 2012 - 18:44

    Hi ,
    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. #12 by vinayak on 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. #13 by Uwe Hesse on 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: http://uhesse.com/2009/05/19/restrict-the-usage-of-new-execution-plans-with-sql-plan-baselines/

  14. #14 by Andy on February 13, 2013 - 16:03

    Hi,
    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. #15 by Wolfgang on March 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. #16 by Uwe Hesse on March 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. #17 by sumanth on March 20, 2013 - 09:51

    what is the mail diff between MOVE and shrink . what is the need of shrink from 10g.

  18. #18 by Uwe Hesse on March 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. #19 by sumanth on March 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. #20 by Uwe Hesse on March 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. #21 by sumanth on March 22, 2013 - 06:09

    Thank you.

  22. #22 by Uwe Hesse on April 2, 2013 - 07:43

    You’re welcome, sumanth :-)

  23. #23 by Michael on June 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. #24 by Uwe Hesse on July 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. #25 by vaibhav on October 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. #26 by Pari on 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. #27 by Uwe Hesse on 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. #28 by Vab on September 30, 2014 - 05:30

    Nicely articulated.

  29. #29 by ishar on December 4, 2014 - 06:56

    Hi Uwe,

    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. #30 by Uwe Hesse on December 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.

  1. Top 10 postings in 2012 « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,975 other followers

%d bloggers like this: