Table B: 3 million, primary key ID
Delete records with ID = Table A IDs from Table B.
Delete from B WHERE EXISTS (SELECT 1 FROM (SELECT ID FROM (SELECT T. ID, rownum rn from a) where rn> 0 and rn <= 50000) AB WHERE. ID = B. ID );
However, the execution plan shows that the COST is large and the bottleneck is the full table scan of Table B.
Requirement: B1... b10 has multiple B Tables (both 3 million), and the serial operation is equivalent to 10 full table scans of B Tables. Because the disk IO performance is poor, the execution of a single DELETE operation may occupy a large CPU, therefore, it cannot be parallel.
Is there any room for optimization? Thank you for your advice!
Execution Plan
----------------------------------------------------------
Plan hash value: 3752040547
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 225K | 15 M | 18309 (1) | 00:03:40 |
| * 1 | hash join right semi | 225K | 15 M | shard k | 18309 (1) | 00:03:40 |
| * 2 | VIEW | 133K | 2604K | 88 (2) | 00:00:02 |
| 3 | COUNT |
| 4 | index fast full scan | P_A | 133K | 911K | 88 (2) | 00:00:02 |
| 5 | table access full | B | 3598K | 171M | 7448 (1) | 00:01:30 |
Bytes -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("A". "ID" = "ID ")
2-filter ("RN"> 0 AND "RN" <= 50000) Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
27479 consistent gets
0 physical reads
0 redo size
11651 bytes sent via SQL * Net to client
645 bytes encoded ed via SQL * Net from client
13 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
168 rows processed