A large amount of data cleaning method for help, 3800W data.

ORACLE version: 10

The official may have a 3800W record table, occupied space is about 11G, according to the time field is removed roughly 1800W records, and time field no index, table without partition, formal environment real-time table in use, not DROP, how to quickly clean?


Have to try:

1)delete from where rownum <10100;In the clear, table space is not released, the need for manual reduction table, speed is very slow.

2) Truncate official environment real-time table in use, not truncate

3) Drop official environment real-time table in use, not DROP


For the master.

Started by Aries at November 17, 2016 - 10:09 AM

After delete
alter table ... shrink space

Posted by Abraham at November 20, 2016 - 10:57 AM

Delete is too slow. Long term use of time field comparative words or add index.

In the dead of night
create table tb1 select * from tbb where tdate > ''
delete .. tbb
Tb1 to TBB

Posted by Priscilla at November 27, 2016 - 11:43 AM

Not partition, then into a partition... The use of online redefinition.

Posted by Hogan at December 02, 2016 - 11:58 AM

1)To get the matching ROWID list, the parallel hint can improve the query speed
2)The ROWID sorting and grouping (for example, divided into 16 groups), you can use the NTILE function block
3)Create multiple JOB (e.g., 16 JOB), according to ROWID respectively corresponding to the set of data parallel deletion. 3800W should take 2-3 hours, of course this and disk performance.

3800W data is not much, our weekly to remove the 300000000 rows of data, because the original table structure design problem, so using the method.
On the release of table space problem, if the target table continues to insert data, so can not be considered temporary, because can reuse the deleted data space.

Posted by Eva at December 04, 2016 - 12:34 PM

The response to 2013-09-30 17:37:31 was removed by the administrator

Posted by Zachary at December 13, 2016 - 1:05 PM

ctas +rename +drop

Posted by Algernon at December 27, 2016 - 1:48 PM

1, From the long-term plan, should do partition according to the actual situation
2, From the demand perspective, as the demands on time not urgent Oh, define a rule, slowly delete.

Posted by Borg at January 09, 2017 - 2:14 PM