1 2 Previous Next 22 Replies Latest reply on Nov 2, 2010 8:43 PM by JustinCave Go to original post
      • 15. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
        764435
        >
        1) Is truncate table table_name = delete table_name + shrink ??
        No, TRUNCATE is consider as DDL, however DELETE has to pass through the SQL Processing.
        Secondly truncate removes all the rows and there is no rollback from truncate command and Delete can be rollback.
        2)Will the both have same performance.
        TRUNCATE is Faster than DELETE.
        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.
        It happens periodically. Not every time. Depends on the Statistics information in catalog table
        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 doubt you would be able to use ALTER TABLE command in PL/SQL
        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..
        You are in wrong impression. Update does not change the rowid, its row-movement which results in changed rowid.
        • 16. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
          795356
          Hii Vinod,
          Yes we can perform alter and all other DDL statements in PL/SQL using execute immediate.

          Begin
          
                    execute immediate 'alter table r_dummy shrink space';
          
          
          
          End;
          Regards
          Raghu

          Edited by: 792353 on Nov 2, 2010 1:44 AM
          • 17. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
            635471
            Vibhor Kumar wrote:
            You are in wrong impression. Update does not change the rowid, its row-movement which results in changed rowid.
            Well, sometimes it can.
            • 18. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
              764435
              David_Aldridge wrote:
              >

              Well, sometimes it can.
              Hmm. I would like to know about the scenario, since the way Oracle has been designed it should not.
              Oracle follows Hot Update concept for UPDATE not an appending concept (i.e expire old row and append new). Its only row-movement which can only change the rowid.
              • 19. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                user503699
                Vibhor Kumar wrote:
                David_Aldridge wrote:
                >

                Well, sometimes it can.
                Hmm. I would like to know about the scenario, since the way Oracle has been designed it should not.
                Oracle follows Hot Update concept for UPDATE not an appending concept (i.e expire old row and append new). Its only row-movement which can only change the rowid.
                I am not aware of "Hot Update" or "Appending Update" terminology but you may want to read about partitioning feature of oracle. Partitioning is designed by Oracle and one can update a partition key, which will cause row movement thereby resulting in change of rowid. I don't have access to an oracle database to demonstrate the scenario but you can build a quick-and-easy test case to see this yourself.
                • 20. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                  JustinCave
                  An update does not generally cause a change to a ROWID (since, as you point out, Oracle is not deleting the old row and inserting a new row). But there are various cases when the ROWID can change (and that set of cases expands with each release). The simplest possible case is an index-organized table
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  create table moving_rowid (
                    2    col1 number primary key,
                    3    col2 number,
                    4    col3 number,
                    5    col4 varchar2(10)
                    6  )
                    7* organization index
                  SQL> /
                  
                  Table created.
                  
                  SQL> insert into moving_rowid values( 1, 2, 3, 'foo' );
                  
                  1 row created.
                  
                  SQL> insert into moving_rowid values( 2, 3, 4, 'bar' );
                  
                  1 row created.
                  
                  SQL> select rowid, col1, col2, col3, col4
                    2    from moving_rowid;
                  
                  SQL> select rowid, col1, col2, col3, col4
                    2    from moving_rowid;
                  
                  ROWID                  COL1       COL2       COL3 COL4
                  ---------------- ---------- ---------- ---------- ----------
                  *BAEADxsCwQL+             1          2          3 foo
                  *BAEADxsCwQP+             2          3          4 bar
                  
                  SQL> update moving_rowid set col1=col1+1;
                  
                  2 rows updated.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> select rowid, col1, col2, col3, col4
                    2    from moving_rowid;
                  
                  ROWID                  COL1       COL2       COL3 COL4
                  ---------------- ---------- ---------- ---------- ----------
                  *BAEADxsCwQP+             2          2          3 foo
                  *BAEADxsCwQT+             3          3          4 bar
                  Note that the FOO row after the update has the same ROWID that the BAR row had before the update and both rows have different ROWIDs after the update than before.

                  Justin
                  • 21. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                    764435
                    This discussion is going on interesting direction:

                    SQL> select rowid, col1,col2,col3,col4 from moving_rowid;

                    ROWID               COL1     COL2 COL3 COL4
                    ------------------ ---------- ---------- ---------- ----------
                    AAAFiAAAEAAAAhgAAA     1     2     3 foo
                    AAAFiAAAEAAAAhgAAB     2     3     4 bar

                    SQL> update moving_rowid set col1=col1+1;

                    2 rows updated.

                    SQL> commit;

                    Commit complete.

                    SQL> select rowid, col1,col2,col3,col4 from moving_rowid;

                    ROWID               COL1     COL2 COL3 COL4
                    ------------------ ---------- ---------- ---------- ----------
                    AAAFiAAAEAAAAhgAAA     2     2     3 foo
                    AAAFiAAAEAAAAhgAAB     3     3     4 bar

                    No change rowid in above reason there is no organisation has been done as per the index.

                    Please note: if you change any indexed column and table has a index for oraganizing data then index will change. This is what happens with partition too.

                    Now, see the example given below with organization index:

                    SQL> update moving_rowid set col1=col1+1;

                    2 rows updated.

                    SQL> commit;

                    Commit complete.

                    SQL> select rowid,col1,col2,col3,col4 from moving_rowid;

                    ROWID                              COL1     COL2 COL3
                    ----------------------------------------- ---------- ---------- ----------
                    COL4
                    ----------
                    *BAEACGwCwQP+                            2           2       3
                    foo

                    *BAEACGwCwQT+                            3           3       4
                    bar



                    Now lets update non-index column:
                    SQL> select rowid,col1,col2,col3,col4 from moving_rowid;

                    ROWID                              COL1     COL2 COL3
                    ----------------------------------------- ---------- ---------- ----------
                    COL4
                    ----------
                    *BAEACGwCwQP+                            2           3       3
                    foo

                    *BAEACGwCwQT+                            3           4       4
                    bar

                    I hope above would have clear the behavior. UPDATE does not change the rowid. Users who see change in rowid is actually movement of data pointer. data pointer changes if the organisation done on the basis of index (and each update changing the index too) or if row-movement has happened.

                    Edited by: Vibhor Kumar on Nov 2, 2010 6:59 PM
                    • 22. Re: Is TRUNCATE TABLE_NAME = DELETE + SHRINK SPACE ??
                      JustinCave
                      Right. No one is arguing that a ROWID will commonly change as a result of an update. If you issue a simple update on a non-partitioned non-clustered heap-organized table, the ROWID will not change. An update on an index-organized table, a partitioned table, or a cluster may change the ROWID.

                      In the past, ROWID used to be far more static. More and more features that Oracle introduces, however, rely on the ability to do things behind the scenes that change where data is stored physically (i.e. things like flashback or index-organized tables) so it's generally not a good idea to expect that a ROWID will remain constant over time.

                      Justin
                      1 2 Previous Next