Pickleball spielen

1. 

   #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. 

   #2 von Uwe Hesse am 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 von coskan am 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 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. 

   #5 von jametong am August 30, 2010 - 15:47

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

   #6 von Uwe Hesse am 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 von Mark Bobak am September 4, 2010 - 14:31

   Nice little demo, Uwe.

   Clear, and to the point.

   -Mark
8. 

   #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. 

   #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. 

    #10 von Uwe Hesse am September 20, 2010 - 09:21

    Thanks for your positive feedback, John 🙂
11. 

    #11 von Kavita am Juni 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 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. 

    #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. 

    #14 von Andy am Februar 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 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. 

    #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. 

    #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. 

    #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. 

    #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. 

    #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. 

    #21 von sumanth am März 22, 2013 - 06:09

    Thank you.
22. 

    #22 von Uwe Hesse am April 2, 2013 - 07:43

    You’re welcome, sumanth 🙂
23. 

    #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. 

    #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. 

    #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. 

    #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. 

    #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. 

    #28 von Vab am September 30, 2014 - 05:30

    Nicely articulated.
29. 

    #29 von ishar am Dezember 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 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. 

    #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. 

    #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. 

    #33 von Sanjay am Januar 30, 2016 - 18:17

    Hi Uwe,

    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.

    Thanks
    Sanjay
34. 

    #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. 

    #35 von Jeyalin Sheeju am Juli 14, 2017 - 12:40

    Thanks a lot for this post… A good read 🙂
36. 

    #36 von Kushal Puri am Dezember 11, 2017 - 06:28

    Hi Uwe,
    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.

1. Top 10 postings in 2012 « The Oracle Instructor
2. Happy New Year 2017! | Uwe Hesse
3. How to identify table fragmentation and remove it ?

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..