CODEWITHSUNDEEP
sqloracle-database
vijay gopal
vijay gopal

Reputation: 11

How to delete 3 billion rows from 2 related tables

I have a table with 5 billion rows (table1) another table with 3 billion rows in table 2. These 2 tables are related. I have to delete 3 billion rows from table 1 and its related rows from table 2. Table1 is child of table 2. I tried using the for all method from plsql it didn't help much. Then I thought of using oracle partition strategy. Since I am not a DBA I would like to know if partioning of a existing table is possible on primary key column for a selected number of id's? My primary key is 64 bit auto generated number.

Upvotes: 1

Views: 136

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

Sounds very dubious.
If it is real use-case then you don't delete you create another table, well defined, including partitioned and you fill it using insert /*+ append */ into MyNewTable select ....
The most common practice is to define partitions on dates (record create date, event date etc.).
Again, if this is a real use-case I strongly recommend that you will reach for real help, not seek for advice on the internet and not doing it yourself.

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

It is hard to partition the objects online(it can be done using dbms_redefinition). And not necessary(with the details you gave).

Best ideea would be to recreate the objects without the undesired rows.

For example some simple code would be like:

create table undesired_data as (select undesired rows from table1);
Create table1_new as (select * from table1 where key not in (select key from undesired_data));
Create table2_new as (select * from table2 where key not in (select key from undesired_data));
rename table1 to table1_old;
rename table2 to table2_old;
rename table1_new to table1;
rename table2_new to table2;
recreate constraints;
check if everything is ok;
drop table1_old and table2_old;

This can be done offlining consumers, but would be very small downtime for them if scripts are ok(you should test them in a test environment).

Upvotes: 3

Related Questions