CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-delete
Asad ch
Asad ch

Reputation: 47

Deletion from Oracle Table

I have a huge amount of data in a table, I am deleting data from table with below query. But it is taking about 2-3 hours to delete about 3-4 million rows. Is there any fast method to delete.

DELETE /*+parallel (aa 64)*/
from ua_contacthistory_bkp PARTITION (ua_contacthistory_bkp_08JUL2018) aa
WHERE aa.CAMPAIGN_CODE='C000000333'

Upvotes: 2

Views: 138

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21043

Note that to perform parallel DML you must enable it using 

 ALTER SESSION ENABLE PARALLEL DML;

or the hint ENABLE_PARALLEL_DML.

According to the used degree of parallelism a good way is to test with say 4, 8, 16 etc. to see if the increase of DOP realy scales, i.e. effectively reduces the elapsed time.

You may find that DOPof 64 is not the best option e.g. if you encounter a disc system bottleneck.

Technically you may also implement a composite partitioning using a LIST subpartitioning schema based on CAMPAIGN_CODE under your current partitioning.

This could either completely avoid the delete in favor of using DROP SUBPARTITION or at least further limit the scope of the DELETEstatement.

Offline Reorganisation

If you can put the partition for a short time offline (i.e. there are no other sessions modifying it) this simple and effective approach will remove the records without a delete.

1) create a temporary table based on your original partition exluding the data you don't want to preserve.

CREATE TABLE TMP as
SELECT * FROM  ua_contacthistory_bkp PARTITION (ua_contacthistory_bkp_08JUL2018) aa
WHERE nvl(aa.CAMPAIGN_CODE,'x') != 'C000000333'

The WHERE predicate selects all data (inclusive NULLs) except of the rows with the code to be deleted.

2) Exchange the new temp table with your partition

alter table ua_contacthistory_bkp exchange partition ua_contacthistory_bkp_08JUL2018
with table TMP
including indexes;

Now the TMP table contains the complete data (you may drop it or whatever you like) and the partition ua_contacthistory_bkp_08JUL2018 all the original data except for the deleted code.

You need to take some care if the partitioned table is indexed. Local index can be created on the TMP table and exchange as well (see including indexes) global indexes must be rebuild.

This approach has positive side effects as you can compress the table or reorder the data (using ORDER BY in the creation of TMP) to optimize the access.

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

I can't know your tables conditions and business logic,

but I'd rather prefer adding an integer type, not null column with a default value of 1, named as active to my table, and prefer updating it instead of deleting( to show or hide all of the row data, have an opportunity to take a glance at the deleted records easily whenever desired ), especially for tables which has millions of rows and lots of columns .

Deleting is known as an expensive operation compared to Updating and Inserting, especially from the point of Undo Data Production Action. For Inserting you need only ROWID column to be inserted into the Undo segment, but as you Deleting you're Inserting all columns to the Undo segment even if you have one-hundred columns in your table. i.e. they're reverse operations. By the way, if Updating just a column(active) is preferred, then Undo segment shall be concerned with ROWID and active columns, that's all.

Upvotes: 1

Related Questions