0

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

APC
  • 144,005
  • 19
  • 170
  • 281
user1595858
  • 3,700
  • 15
  • 66
  • 109
  • Try to use `EXPLAIN SELECT 1 FROM schema.adress t2 WHERE t2.flngEntityKey = 9909 ..... ` to find out the performance of the query and create `indexes` where ever required. this will definitely improve performance. – Raj Paliwal Nov 08 '19 at 11:48
  • Think without an execution plan and cardinalities it's hard to optimize. Avoiding the selfjoin could help. – Thomas Strub Nov 08 '19 at 11:48
  • Having triggers/Indexes on the table might slow down the Insert/Update/Delete performance. Please disable all the indexes/triggers before DML operation. Rest, Optimizers are smart enough to choose the best execution plan for them to improve the performance. – Ankit Bajpai Nov 08 '19 at 12:01
  • The problem might also (because the deleted rows have to be written in the rollback segment) that you quickly run into RAM-issues. So doing the delete in a loop with aditional condition `rownum < 1000` and intermediate `commit`s could also help. – Radagast81 Nov 08 '19 at 12:13
  • Please provide the suggested statement – user1595858 Nov 08 '19 at 13:07
  • 2
    So how many of your one billion rows is *"some matching rows"*? – APC Nov 08 '19 at 14:08

0 Answers0