0

I've a table with 230 million records and from this table I need to delete 300 records everyday on the basis of some condition. Now, this table does not have index created on it. To delete 300 records I've created a job which does deletion part. Client is not okay to create index(or partitioned index) on this particular table.

As of now, I'm deleting records from this table using CURSOR FOR method but it is running for 3 hrs to delete 300 records.

Could you please suggest any method to delete the records from huge table which does not have index created on it.

OracleForLife
  • 7
  • 3
  • Cursor would be the least performant option. How do you know which rows to delete? – HoneyBadger Jun 10 '22 at 13:19
  • There is another table which has id's to be delete, we fetch 300 records and feed these id's to the table from where we need to delete. For this purpose I'm using FOR LOOP CURSOR. – OracleForLife Jun 10 '22 at 13:22
  • *this table does not have index created on it* - just to clarify, does it even have a primary key (which by definition is indexed)? Do you have the primary key ids to identify the records that need to be deleted? The real problem may be using a cursor here, which sound complete unnecessary (per note by HoneyBadger above - that would be the worst option all things being equal). – topsail Jun 10 '22 at 15:54

1 Answers1

3

Instead of using loops you could go for a simple EXISTS:

DELETE 
  FROM main_table mt
 WHERE EXISTS (SELECT 1
                 FROM another_table
                WHERE some_id = mt.same_id);

or JOIN the tables:

DELETE 
  FROM main_table
 WHERE rowid IN (SELECT mt.rowid
                   FROM main_table mt
                        INNER JOIN other_table ot ON mt.some_id = ot.same_id);
Gnqz
  • 3,292
  • 3
  • 25
  • 35