Re: Can a partial truncate be done?

deanbrown3d_at_yahoo.com wrote:
> I heard some talk of partial truncates, that are fast and avoid all the
> role-back delay. Can someone give me a pointer here where to get
> started? I heard its an option now in recent versions of server, is
> this true?
>
> By 'partial truncate' I mean to remove a selection of records fast,
> like a regular table truncate, but leaving other records intact.
> Thanks a lot!
>
> Dean
>

Probably what you heard about was using the exchange partition feature: You have a partitioned table. The fastest way to partially delete data in that table is to swap one partition with an empty table and vice versa.

So for you this might mean that you create a partitioned table that has the same structure as your table with one single partition. Then you insert those rows from your original table that you want to keep, create the indexes on the partition that exist for your original table and finally exchange your table with the partition that now contains the data you want to keep. To reduce redo generation you might want to add nologging to the insert.

alter table <partitioned table> exchange partition <partition name> with table <your original table>.

A small example of what I mean:

SQL> create table part_demo (id int, text varchar2(10))

   2 partition by range (id)
   3* (partition p_1 values less than (maxvalue)) baer_at_DEMO10G>/

Table created.

baer_at_DEMO10G>create table demo (id int, text varchar2(10));

Table created.

baer_at_DEMO10G>begin

   2 for i in 1..1000 loop
   3 insert into demo values (i, lpad (to_char(i), 10,'X'));    4 end loop;
   5 end;
   6 /

PL/SQL procedure successfully completed.

baer_at_DEMO10G>insert into part_demo select * from demo where mod(id, 3) = 0;

333 rows created.

baer_at_DEMO10G>alter table part_demo exchange partition p_1 with table demo;

Table altered.

baer_at_DEMO10G>

HTH Holger Received on Wed Jan 19 2005 - 07:32:06 CST