1 2 Previous Next 22 Replies Latest reply on Nov 2, 2010 8:43 PM by JustinCave

    Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??

    795356
      Hii Adepts,

      1) Is truncate table table_name = delete table_name + shrink ??

      2)Will the both have same performance.

      3)Can I use shrink space all the time whenever I perform deletion of rows in large number(I've enable row movement).So that I can adjust HWM and hence utilize the space after deletion.

      4)Now ,I'd like to change my pl/sql blocks adding 'alter table table_name shrink space' whenever I perform delete in large number.Is it worth doing so?

      I've gone thru this http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35203106066718 and found it very useful.

      5)Also its mentioned that whenever we update a column rowid will be changed .But I've identified that its not so..

      SQL> create table r_dummy(a number) enable row movement;
      
      Table created.
      
      SQL> Insert into r_dummy values (10);
      
      1 row created.
      
      SQL> commit;
      
      Commit complete.
      
      SQL> select rowid,a from r_dummy;
      
      ROWID                       A
      ------------------ ----------
      AAAOoOAAOAABIMoAAA         10
      
      SQL> update r_dummy set a = 50 where a = 10;
      
      1 row updated.
      
      SQL> commit;
      
      Commit complete.
      
      SQL> select rowid,a from r_dummy;
      
      ROWID                       A
      ------------------ ----------
      AAAOoOAAOAABIMoAAA         50
      
      SQL> 
      6) When does the rowid will get changed???
        • 1. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
          user503699
          792353 wrote:
          Hii Adepts,
          So many questions in a single thread ;) Let me see how I can help.
          >
          1) Is truncate table table_name = delete table_name + shrink ??
          Would like to know the objective behind this question before giving a valid answer.
          >
          2)Will the both have same performance.
          It depends.
          3)Can I use shrink space all the time whenever I perform deletion of rows in large number(I've enable row movement).So that I can adjust HWM and hence utilize the space after deletion.
          You can but are you sure you are solving a real problem by doing that? In other words, what is the "real problem" you are trying to solve?
          4)Now ,I'd like to change my pl/sql blocks adding 'alter table table_name shrink space' whenever I perform delete in large number.Is it worth doing so?
          Only you can answer if it is worth as you know the nature of your data and the way it is accessed (and if there are any problems you are facing at present)
          I've gone thru this http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35203106066718 and found it very useful.

          5)Also its mentioned that whenever we update a column rowid will be changed .But I've identified that its not so..
          Where does it say so?
          >
          SQL> create table r_dummy(a number) enable row movement;
          
          Table created.
          
          SQL> Insert into r_dummy values (10);
          
          1 row created.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> select rowid,a from r_dummy;
          
          ROWID                       A
          ------------------ ----------
          AAAOoOAAOAABIMoAAA         10
          
          SQL> update r_dummy set a = 50 where a = 10;
          
          1 row updated.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> select rowid,a from r_dummy;
          
          ROWID                       A
          ------------------ ----------
          AAAOoOAAOAABIMoAAA         50
          
          SQL> 
          6) When does the rowid will get changed???
          a) When the table is reorganized OR
          b) When you update a column value that is used as a partition key in a partitioned table.
          • 2. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
            795356
            I've verified and shrink space changes rowid.

            Edited by: 792353 on Nov 1, 2010 9:30 PM
            • 3. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
              795356
              The only objective of this question to lower HWM and hence utilize the space after deleting rows in large number.As truncate table does this ,I'd like to know can the same be acheived using delete+shrink .Hope I made it clear now.
              • 4. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                user503699
                792353 wrote:
                So,does the rowid get changed after we perform ;alter table r_dummy shrink space??
                It may change.
                • 5. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                  795356
                  I'm just upgrading my knowledge and I havent faced any problem with this.Upto now I havent find any flaws in using this.But from this site(http://www.dba-oracle.com/t_enable_row_movement.htm) I came to there can be some performance problems.Could please tel me how it can be?


                  "Beware that using "enable row movement" can cause Oracle to move rows to discontinuous data blocks, causing a performance problem. "

                  ???How it will degrade performace?
                  • 6. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                    user503699
                    792353 wrote:
                    I'm just upgrading my knowledge and I havent faced any problem with this.Upto now I havent find any flaws in using this.But from this site(http://www.dba-oracle.com/t_enable_row_movement.htm) I came to there can be some performance problems.Could please tel me how it can be?


                    "Beware that using "enable row movement" can cause Oracle to move rows to discontinuous data blocks, causing a performance problem. "

                    ???How it will degrade performace?
                    Hmm. You may want to check with the author of that website the reasons behind his claims ;)
                    Anyways, that statement about performance getting affected due to row movement (which can be caused by SHRINK SPACE) is correct statement. But as most of the things are, it depends. It is not a universal fact. It (performace degradation) may happen because the movement of rows may affect ordering of data in the table, which in turn may make some indexes on the table less attractive (or more costly) for CBO. This could result in some queries that were using index before (the rows are moved), may now either start using another index or go for a full table scan. This change in execution plan is what may cause performance degrade.
                    Hope this helps.
                    • 7. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                      795356
                      Ya I got an idea with your explanation.So,what I understood is shrink space will have an impact on index and It scatters insertion in the index blocks??Am I correct? This is something like reverse index?????Hope I'm not messing up two different concepts.

                      Edited by: 792353 on Nov 1, 2010 11:07 PM
                      • 8. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                        user503699
                        792353 wrote:
                        Ya I got an idea with your explanation.So,what I understood is shrink space will have an impact on index and It scatters insertion in the index blocks??Am I correct? This is something like reverse index?????Hope I'm not messing up two different concepts.
                        I think you are.
                        The SHRINK SPACE is used with a table and may affect the order of rows in the table. Not sure why are you talking about index blocks.
                        • 9. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                          795356
                          As we delete rows from the table the respective rows should be deleted from the index,aren't they?And you said that index range scans can get affected by using shrink space.So,I got messed up.
                          • 10. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                            795356
                            Now I got a new doubt,
                            As the rowid of a particular row changes after we execute 'ALTER TABLE SHRINK SPACE' what about the rowid within the index?(As the index is two dimensional matrix one column with column contents and the other with rowids)Does the rowid within the index gets changed respectively??Please clarify this.

                            Edited by: 792353 on Nov 2, 2010 12:15 AM
                            • 11. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                              user503699
                              792353 wrote:
                              Now I got a new doubt,
                              As the rowid of a particular column changes after we execute 'ALTER TABLE SHRINK SPACE' what about the rowid within the index?(As the index is two dimensional matrix one column with column contents and the other with rowids)Does the rowid within the index gets changed respectively??Please clarify this.
                              RowID is for a row and not for a column. As for the impact of SHRINK SPACE on index, why don't you test and see what happens? ;)

                              Hint: If rowid change (caused by SHRINK SPACE) is not cascaded to indexes, will the indexes remain valid/usable after the operation?
                              • 12. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                                user503699
                                792353 wrote:
                                As we delete rows from the table the respective rows should be deleted from the index,aren't they?And you said that index range scans can get affected by using shrink space.So,I got messed up.
                                Hope you have managed to clarify your doubt now. Basically DELETE and SHRINK SPACE are two independent operations. My statement about the index range scans was in the context of SHRINK SPACE and not DELETE.
                                • 13. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                                  795356
                                  Great,Thanks alot you have answered all of my trivial questions with patience.Now I'll do R&D on this and will come back with more questions.

                                  (Could you tel me how to use smilies here?I waz looking for that option but couldn't find it)
                                  • 14. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                                    user503699
                                    792353 wrote:
                                    Great,Thanks alot you have answered all of my trivial questions with patience.Now I'll do R&D on this and will come back with more questions.

                                    (Could you tel me how to use smilies here?I waz looking for that option but couldn't find it)
                                    In that case, you may want to mark the question as "answered".
                                    A colon (:) followed by closing round bracket is one way. I think you will find more details in the FAQ link in the sticky at the top of the forum.

                                    Edited by: user503699 on Nov 2, 2010 12:51 PM
                                    1 2 Previous Next