CODEWITHSUNDEEP
sqloracle-databasedatabase-performance
Ahmet Karakaya
Ahmet Karakaya

Reputation: 10139

Oracle SQL Delete Operation Performance

I have two tables tableA(33M records) and tableB (270K records), I want to delete all records in tableA which also exist in tableB. So write a SQL statement below. I think It should be modified since it table more than 1 hour to remove them all. Do you have an idea if it is usual for these kind of operation. Note: primary key of both tables is id.

delete from tableA where id in (select id from tableB);

Here is the sql statement explain 

|   0 | DELETE STATEMENT        |                        |   289K|  7341K|       | 85624   (1)| 00:17:08 |                                                                                                                                                                                                   
|   1 |  DELETE                 | tableA |       |       |       |            |          |                                                                                                                                                                                                   
|   2 |   MERGE JOIN            |                        |   289K|  7341K|       | 85624   (1)| 00:17:08 |                                                                                                                                                                                                   
|   3 |    INDEX FULL SCAN      | SYS_C0015397           |    36M|   455M|       | 84050   (1)| 00:16:49 |                                                                                                                                                                                                   
|*  4 |    SORT JOIN            |                        |   289K|  3670K|    11M|  1574   (1)| 00:00:19 |                                                                                                                                                                                                   
|   5 |     INDEX FAST FULL SCAN| SYS_C0015401           |   289K|  3670K|       |   193   (2)| 00:00:03 |                                                                                                                                                                                                   
---------------------------------------------------------------------------------------------------

Upvotes: 0

Views: 110

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

That's an interesting execution plan. You don't see merge joins often because they usually require a sort of the data first, but in this case only one data set needs to be sorted because it's accessed via an index fast full scan (which returns unsorted data) instead of an index full scan.

Most of the cost is associated with reading the SYS_C0015397 index via an index full scan, and I'd guess that the optimiser has done the arithmetic for a pair of fast full scans and a hash join and rejected it. Still, I'd see if that can be hinted with:

delete /*+ no_use_merge(tablea) */ from ...

I'm not sure if that's enough to get a hash join, but see if the explain plan tries something other than a merge join there.

Is the join column on tablea a unique or PK?

Upvotes: 1

Panu Oksala
Panu Oksala

Reputation: 3438

Use exists or insert saved data into new table (B), drop old table (A) and rename the new table(B) as a table(A).

Upvotes: 0

Related Questions