Oracle SQL Delete Operation Performance

Question

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 |                                                                                                                                                                                                   
---------------------------------------------------------------------------------------------------

Please post the results of "explain plan for <your delete statement" and "select * from table(dbms_xplan.display)" — Rob van Wijk
– Rob van Wijk, Commented Dec 11, 2013 at 12:50
I see the row estimate of 999 is incorrect -- should be around 270k. Analyse tableb and try again -- a hash join might be produced, and that might be better. — David Aldridge
– David Aldridge, Commented Dec 11, 2013 at 14:31
Does it help when you calculate statistics of tables: EXEC DBMS_STATS.GATHER_TABLE_STATS ('tableA') and EXEC DBMS_STATS.GATHER_TABLE_STATS ('tableB') — Wernfried Domscheit
– Wernfried Domscheit, Commented Dec 11, 2013 at 16:12

David Aldridge · Accepted Answer · 2013-12-12 07:46:35Z

1

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?

answered Dec 12, 2013 at 7:46

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Panu Oksala · Accepted Answer · 2013-12-11 14:08:57Z

0

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

answered Dec 11, 2013 at 14:08

Panu Oksala

3,4761 gold badge20 silver badges28 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Delete Operation Performance

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related