1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Truncate Partially

Discussion in 'SQL PL/SQL' started by jagadekara, May 23, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Good Morning...

    Is it possible to truncate partial? I mean, I have a table test1 which has 280000, So I want to truncate 145000 records which are before 2014 year. I know it is possible with delete but it will take more time. So is there any chance to truncate?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    The table is partitioned?

    If the table isn't partitioned, then partially it is impossible to delete data (to clear partition for example).


    It is possible to use such scenarios:

    Note :
    1) to use package deleting, with use of collections and FORALL

    simple exapmle :
    Code (SQL):


    DECLARE
      TYPE t_tmp IS TABLE OF urowid;
      l_tmp t_tmp;
      cur sys_refcursor;
    BEGIN

    /*
    to gain the best effect, it is necessary to use sorting according to rowid, as then the probability of is higher that rows  from one block (or adjacent blocks) will be is in selection by a row
    */

        OPEN cur FOR SELECT rowid  FROM test1 WHERE your_clause ;--order by 1 ;
        loop
            fetch cur bulk collect INTO l_tmp LIMIT 10000; -- your batch size  in rows
            exit WHEN l_tmp.COUNT =0 ;

            forall i IN 1 .. l_tmp.COUNT
              DELETE /*+ rowid */  FROM test1 WHERE rowid = l_tmp(i);

                commit;
           
        END loop;    
        close cur;
    END;
     
    2) dbms_prarallel_execution

    3) note : if the table independent (there are no links)
    create table test_old as select * from test1
    truncate test1
    insert into test1 as select * from test_old where...

    -- if there were indexes, then it is necessary to recreate.

    drop table test_old


    or

    rename test1 to test1_old;
    create table test1 as select * from test1_old where ...

    -- if there were indexes, then it is necessary to recreate.


    4) If the table "flat" also isn't present constraints, indexes, then it is possible to use parallel dml

    alter session enable parallel dml;
    delete /*+ parallel */ from test1;
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think you don't understand what truncate does -- truncate drops all but the initial extent of the table and resets the high water mark to what it was when the table was created. There is no 'partial' truncate for a non-partitioned table. You can truncate a partition to drop all of its extents except the initial but that still affects the entire partition.


    You could create a temporary copy of the table minus the records you want deleted, truncate the original table and insert the data from the temporary copy, then drop the temporary copy. Or you could delete the data.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Since you are looking to remove data for a given date range (i.e. before 2014), this sounds like something you may be doing annually. If so, it would be best to partition that table by year. If the table were partitioned by year, the operation to remove prior year data will be as simple as dropping the relevant partition.

    Since you are indicating that you can't take the hit from a delete, what might work best is to perform an online table redefinition to change the current table to be partitioned by year. By design, online redefinition has a very minimal time period during the operation where the table is unavailable. Once the redef is completed, drop the partition holding the 2014 data.
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks All for your valuable suggestions...