purging data and reclaiming space

[Scunningham99]

Hi

Our developers intend to delete data from a number of tables. For example we have a table which contains 140million rows. We intend to delete approx 85million rows from this table.

Our plan is to create another tablespace, so once the delete is complete we can issue alter table <table name> move tablespace NEW_TABLESPACE;

Then rebuild the indexes.

My question is this the best method for reclaiming the space and to reset the highwater mark?

Would this be a better method compared to import/export? in terms of speed and results?

kind regards

simon

Sy UK

 

[SantaMufasa]

>>> is this the best method for reclaiming the space and to reset the highwater mark?

Yes, by far

>>> Would this be a better method compared to import/export?
in terms of speed and results?

Speed...Yes. Results...No difference

Let us know if you have additional questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Scunningham99]

thanks dave what I expected ... just needed reassurance..

kind regards



Sy UK

 

[Beilstwh]

Just curious, if you are purging 85 million rows, how long until you have added 85 million more. If you are looking at less then a year and since oracle will reuse deleted space, why bother. If it is a perm reduction then reclaim.

Bill
Oracle DBA/Developer
New York State, USA

 

[stefanhei]

I think the fastest way would be to create a table in the new tablespace with the as select clause where you include only the rows you need. Then drop the original table, create indexes and constraints.

That way you only work with ddl an no dml, which should be faster and less resource intenstive (undotablespace...)

Stefan

 

[carp]

If you take Stefan's approach, don't forget that you will need to drop the old table, rename the new table, then recreate any foreign key dependencies that referenced the old table. Also, before you drop the old table, be sure to install any triggers on the new table that were built on the old table. Finally, you will need to recompile any code objects that referenced the old table.
All in all, I think your original approach may be less trouble (as well as less DML). Of course, this also assumes that this isn't just a bare table that nothing references (which, given the number of rows you indicate, seems like a fairly safe assumption).

 

[carp]

If you want to move the HWM and leave the table in its current position, you could use a variation on Stefan's approach:

1.  CREATE TABLE my_holding_pen 
        AS SELECT * 
             FROM my_table 
            WHERE <whatever constraints will retain the data you want to keep>;
2.  TRUNCATE TABLE my_table;
3.  INSERT INTO my_table SELECT * FROM my_holding_pen;
4.  DROP TABLE my_holding_pen;

However, if you want to move the table to a new tablespace, then your original approach is splendid.

 

[carp]

To clarify - the above is something you could do if you felt more comfortable with a more mechanical approach. However, even if you want to keep the table in the same tablespace, deleting undesired rows and then doing an in-place move would accomplish the same thing with fewer steps.
Sorry for the clutter.

Note to self: Never, EVER start posting before the coffee kicks in!

 

[karluk]

Stefanhei's suggestion looks like by far the best solution of the alternatives proposed so far. You could create the new table with the "nologging" option to make the copy even faster. Then, when you have the rows that you want, drop the old table, rename the new table, turn loggin on, create indexes and other constraints, and you're ready to go.

If you decide to go with the delete -> move tablespace approach, at the very least you should drop indexes before doing the delete. Updating indexes 85 million times is likely to be hideously slow.

 

[karluk]

For what it's worth, this thread covers essentially the same issue that Terry Hoey had back in my early days on tek-tips. He had very good results with stefanhei's approach. See thread186-81219 for details.

 

[carp]

Given the volume involved, I would not be surprised.

 

[SantaMufasa]

I should probably clarify things as I see them so that subsequent readers that stumble onto this thread do not misunderstand what they've read here:

1) My first reply was considering just the comparison between using "alter table...move...;" versus export/import. With the attachment of the "PARALLEL NOLOGGING" options to the "alter table...move..." option, it is lightening fast compared to exp/imp. That post, however, did not take into account the intensive task of removing 85M unwanted rows.

2) If one is deleting 85M rows, retaining 55M rows, using the DELETE statement causes 85M rows of rollback entries, equal in size to the full length of all 85M rows! Alternately, the following scheme is MUCH more efficient:

1) CREATE TABLE <workname> AS
SELECT * FROM <original table>
 WHERE <condition matching 55M rows to keep>;
(virtually no rollback results from this since INSERT has negligible pre-change image)

2) TRUNCATE <original table> -- no rollback created !

3) INSERT INTO <original table> SELECT * from <workname>;
(again, negligible rollback results and all data will be compressed below the new high-water mark since you TRUNCATE the table prior to the INSERT.)

Hopefully, this summarises and clarifies things a bit.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]