One of most operations we are performing is “Deleting many rows from oracle big tables” ;
Ths most common / easier query we always have in mind is :
DELETE FROM MY_BIG_TABLE WHERE MY_COLUMN
The problem the query can take hours to finish and may generate a huge amounts of archives logs;
3 solutions can be used here:
create table MY_NEW_BIG_TABLE NOLOGGING as select * from MY_BIG_TABLE where … ;
drop table MY_BIG_TABLE ;
rename MY_NEW_BIG_TABLE to MY_BIG_TABLE ;
create index old_table_idx1 on MY_BIG_TABLE (My_col1,My_col2) NOLOGGING parallel 2 tablespace INDX;
1- A new table is created Without log generated during the operation.
2- Move the data to a new table, drop and then rename the old table by a new one.
3- Create indexes as fast as possible , without log during the creation of the index.
Partition the data, do a parallel delete. Each partition will use its own rollback segment, each will run in parallel.
Partition the data so that you can do a Truncate the partition instead of DELETE.