Fast delete- Best practice


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 :

BEGIN
DELETE FROM MY_BIG_TABLE WHERE MY_COLUMN
COMMIT;
END;
/
The problem the query can take hours to finish and may generate a huge amounts of archives logs;

3 solutions can be used here:

Solution1:
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.

Solution 2:

Partition the data, do a parallel delete. Each partition will use its own rollback segment, each will run in parallel.

Solution 3:

Partition the data so that you can do a Truncate the partition instead of DELETE.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s