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.
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 (
FROM (SELECT ROWID rid,
ROW_NUMBER () OVER (PARTITION BY centrale, operazione ORDER BY data_inizio DESC)
WHERE rn > 50);