bulk delete in performance way with incremental fashion

121 Views Asked by At

We want to delete some matching rows within the same table seems to have a performance issue as the table has 1 billion rows.

Since it Oracle database, we can use PLSQL as well to incrementally delete, but we want to see what options available just using sql to improve the performance of it.

DELETE
FROM   schema.adress
WHERE key      = 6776
AND       matchSequence  = 1
AND       EXISTS  
(
                SELECT 1                         
                FROM   schema.adress t2
                WHERE t2.flngEntityKey  = 9909
                AND       t2.matchType  = 'NEW'
                AND       t2.matchType  = schema.adress.matchType
                AND       t2.key    = schema.adress.key
                AND       t2.sequence   = schema.adress.sequence
)

Additional details

Cardinality is 900 Million rows

No triggers

0

There are 0 best solutions below