Delete - Large number of records

[ricardocmoreno]

Environment information:
* Oracle version: 10.2.0.3
* Operating system: Red Hat PPC 64


Gentlemen,
]
Good morning,

I need to make an purge in a table that has 696.276.322 lines.
My instance works 24x7x365
and the table that contains the data I need to do the purge is very accessible at any time.

I need to make the ombematus of +/- 10,000,000 of lines.
I have tried to do with a simple delete with a filter by a date range that need to delete. But it was taking too long, besides locking the table at a few moments. Outside archive generation that increased considerably.
and after deletes I would have to run some DBMs to return the deleted space to the disk, since the delete does not return the space to the disk, only erases the records what would utter the inverse in the case of a truncate.

My question: Is there a more efficient and faster way to do this purge?
Any tips? Suggestion?

Thanks.

[gilbertoca]

Environment information:
* Oracle version: 10.2.0.3
* Operating system: Red Hat PPC 64


] Gentlemen,

Good morning,

I need to make an purge in a table that has 696.276.322 lines.
My instance works 24x7x365
and the table that contains the data I need to do the purge is very accessible at any time.

I need to make the ombematus of +/- 10,000,000 of lines.
I have tried to do with a simple delete with a filter by a date range that need to delete. But it was taking too long, besides locking the table at a few moments. Outside archive generation that increased considerably.
and after deletes I would have to run some DBMs to return the deleted space to the disk, since the delete does not return the space to the disk, only erases the records what would utter the inverse in the case of a truncate.

My question: Is there a more efficient and faster way to do this purge?
Any tips? Suggestion?

Thanks.

Exist restrictions on this object? (Trigger / PLSQL / Constraints)
You can enable, operate, and then rehabilitate again.

You have [url=http://download.oracle.com/docs/cd/B193 ... m#i2112841]WITH READ ONLY


You can use the Rownum clause on your filter and delete gradually.

The PL / SQL galera can help here TB, there are tuning techniques in this area.
Another alternative is to create an equal object filtering the required lines, truncates the old one and create a synonym for the new (I think we can rename, I do not know for sure!)

Select all

 
1. Crie uma nova tabela 
create table <newTable> as select * from <oldTable> where ... 
2. Truncar e Deletar a antiga 
truncate oldtable --sem necessidade de segmento de rollback 
drop oldtable 
3. Renomeie 
rename <newTable> para <oldTable> 
4. Crie índices necessários 
create the index. 

Gilberto

[ricardocmoreno]

I opened a service request next to Oracle asking for a brief and follows the answer.

Select all

Please see the action plan below. 
 
Thanks, 
Hazem Ibrahim 
Global Customer Services 
 
ACTION PLAN 
=========== 
The following options can be used to delete a large number of records from a table: 
 
1) The ideal solution is to convert the table from non-partitioned to a partitioned table by day (or by week, by month - for 
period which you want delete/drop), and instead of delete rows, you can drop 
the old partition which you don't want to keep. You can drop or place its 
tablespace in offline mode, By this you keep data, without any access. This can 
be done *online* using DBMS_Redefinition package. 
 
please refer to the following: 
Note 472449.1 - How To Partition Existing Table Using DBMS_Redefinition 
Note 177407.1 - How to Re-Organize a Table Online 
 
Oracle® Database Administrator's Guide 
10g Release 2 (10.2) 
Part Number B14231-02 
15 Managing Tables 
Example 1 Redefines a table by adding new columns and adding partitioning 
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#CJAEBFJA 
 
2) You can break the delete operation to many by deleting a certain number of rows each time: 
Ex: 
SQL> delete from big_table 
where <condition> 
and rownum < 10000; 
 
** The above will delete 10000 rows at a time 
 
3) You can export/import 
a) Export the table with query option 
b) Truncate the table 
c) Import the table back 
 
However this operation has a higher risk and will not allow access to the table for some 
time

I will make the first option.

[gilbertoca]

1) The ideal Solution is to convert the table from non-partitioned to the partitioned table by day (or by month, by month - for
Period Which You Want Delete / Drop), and instead of delete rows, you can drop
The Old Partition Which You Do not Want to Keep. You can Drop or Place ITS
Tablespace in offline mode, by this you keep date, without any access. This Can
Be Done * Online * Using DBMS_REDEFINITION PACKAGE.


[] s

Well, you did not make it clear that you had this optional (which only exists in the Enterprise version ).
So I passed you as usual in these cases!
In any case, post here the procedures you have adopted (of course, if you do not infrigure some standard of your company!), So we can have as a reference.

Gilberto

[ricardocmoreno]

Firmness ...

I am doing the procedures for two days because the table is very large.
even working in Parallel still takes a little ...
As soon as I get the result I will post here for the staff ..

Abçs

[alexmaior]

Hello Ricardo, Alright,

You could post an example of the solution you performed.

grate

Alex Oliveira