Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Optimizing a DELETE statement for large number of records

Posted on 2008-10-16
12
Medium Priority
?
2,956 Views
Last Modified: 2013-12-07
The column OPERAZIONE in the NEREPSTA_STORICO table can assume 2 values: 'IMPORT' or 'EXPORT'.

The column CENTRALE in the NEREPSTA_STORICO table can assume 10500 different values.

The DELETE statement you can see attached, keeps in the NEREPSTA_STORICO table keeps max 50 records for every different CENTRALE with OPERAZIONE = 'IMPORT', and max 50 records for every different CENTRALE with OPERAZIONE = 'EXPORT'.

So we can have maximum 50 * 2 * 10500  = 1050000 records on the NEREPSTA_STORICO table.

Explaination:

50 => max 50 record for every distinct couple (CENTRALE, OPERAZIONE)
2 => two types of OPERAZIONE ('IMPORT' or 'EXPORT')
10500 => the number of different CENTRALE values

How can I optimize the DELETE statement?  At the end of the transitory period, I'll have always 1050000 records, and I'm afraid of going deadlock to delete the records.

DELETE FROM nerepsta_storico@linkmaster
            WHERE ROWID IN (
                     SELECT rid
                       FROM (SELECT ROWID rid,
                                    ROW_NUMBER () OVER (PARTITION BY centrale, operazione ORDER BY data_inizio DESC)
                                                                           rn
                               FROM nerepsta_storico@linkmaster)
                      WHERE rn > 50);

Open in new window

0
Comment
Question by:CRISTIANO_CORRADI
  • 6
  • 6
12 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22729131
What is the expected number of records that get deleted?

If this number is large, and If you dont have dependent objects on nerepsta_storico, then can you try to create a table with only the required records using CTAS, rename the original table to backup and rename the temp table to nerepsta_storico?

0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22729317
The expected number of deleted records for every delete statement is:

one for every couple (CENTRALE, OPERAZIONE), where CENTRALE can assume 10550 different values, and OPERAZIONE can assume two values: 'IMPORT' or 'EXPORT'.

The objective is to keep only 50 more recent records for every CENTRALE with OPERAZIONE = 'IMPORT', and only 50 more recent records for every CENTRALE with operazione = 'EXPORT'.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22729441
10550 * 2;
is it slow?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:CRISTIANO_CORRADI
ID: 22730037
In a few months, the system will delete about 20000 records selected between over 1000000 records: is it not possible to optimize the query?  The clause "WHERE ROWID IN" is too heavy?  Have I to substitute it with "EXISTS" clause?
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 22737245
IN and EXISTS may not make a difference, as EXISTS is used in most of the cases to push the usage of an INDEX.
In my opinion, you have to think of a way to avoid the delete, say
- partition the table effectively and truncate the partition
- or create a new table with required data.(This approach is worth only if the required number of records are much lesser than the number of records to be deleted.)
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22738522
OK.  Any other clues?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22738868
Can you do this delete in the remote database directly, instead of going through the db-link?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22738875
If it is possible you may try to use parallelism in the delete, by altering the table to give a degree of parallelism.
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22738915
@sujith80: No, I can't do this delete in the remote database :-(   What do you mean by using parallelism in the delete?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22738993
Read this link for details on parallel DML in oracle.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#sthref2120

But, i am not quite sure whether it will work for you, as you are doing the delete over a db-link(never tried it).
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22739399
I was trying to use the WITH clase, but with the DELETE statement it doen't work, why??
WITH s AS
     (SELECT rid
        FROM (SELECT ROWID rid,
                     ROW_NUMBER () OVER (PARTITION BY centrale, operazione ORDER BY data_inizio DESC)
                                                                           rn
                FROM nerepsta_storico@linkmaster)
       WHERE rn > 5)
SELECT RID FROM S

Open in new window

0
 

Author Closing Comment

by:CRISTIANO_CORRADI
ID: 31506644
Thanks for your explaination!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question