CODEWITHSUNDEEP
sqlperformanceoracle-database
user747858
user747858

Reputation:

how can i improve the performance of this oracle sql delete query?

I got to delete some unwanted rows from a table based on the result of a select query from another table

DELETE /*+ parallels(fe) */  FROM fact_x fe
WHERE fe.key NOT IN(
  SELECT DISTINCT di.key
  FROM dim_x di
  JOIN fact_y fa
  ON fa.code         = di.code
  WHERE fa.code_type = 'ABC'
 );

The inner select query returns 77 rows and executes in few milliseconds. but the outer delete query runs forever(for more than 8 hrs). I tried to count how many rows got to be deleted by converting the delete to select count(1) and its around 66.4 million fact_x rows out of total 66.8 million rows. I am not trying to truncate though. I need to retain remaining rows.

Is there any other way to achieve this? will deleting this by running a pl/sql cursor will work better?

Upvotes: 1

Views: 1255

Answers (3)

DAA
DAA

Reputation: 1

Try /*+ parallel(fe) */. No "S".

Upvotes: 0

Hans Kesting
Hans Kesting

Reputation: 39338

Could you add a "toBeDeleted" column? The query to set that wouldn't need that "NOT IN" construction. Deleting the marked rows should also be "simple".

Then again, deleting 99,4% of the 67 million rows will take some time.

Upvotes: 1

JNK
JNK

Reputation: 65217

Would it not make more sense just to insert the rows you want to keep into another table, then drop the existing table? Even if there are FKs to disable/recreate/etc. it almost certain to be faster.

Upvotes: 7

Related Questions