How to remove 'wasted rows' after delete in Oracle SQL database

Question

In Oracle sql database, a process in our system deleted (not truncated) approx 2 million rows from a table. This resulted in a huge number of 'wasted rows' causing the queries running on that table to take more than 9 hours which usually get over in 5 minutes. Upon checking, we found that the size of total number of actual rows was of around 2600MB whereas the overall table including 'wasted rows' had a size of 3700MB.

Please let me know what is the best way to delete rows and then get rid of 'wasted rows' so that we don't have to rebuild the table every time.

Well technically this are not a `wasted rows` but a *free space* that can be reused in further insert. You can reclaim this space by reorganizing the table using `ALTER TABLE xxx MOVE`. Based on your description of the query behaviour, I'd recommend further investigation of the [execution plan](https://stackoverflow.com/a/34975420/4808122) as it is quit probable there are other problems than the free space. — Marmite Bomber, Nov 17 '19 at 10:36

score 3 · Accepted Answer · edited Nov 17 '19 at 17:49

3

Let's simulate your case with a test table created with some data

create table tst as 
select 
rownum id, lpad('x',1000,'y') pad
from dual 
connect by level <= 100000;

The table consist of 15K blocks

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
     15360

If we delete all rows, the table size remains the same

delete from tst;
commit;

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
     15360

After reorganising the table the table size goes down as the free space is removed.

alter table tst MOVE;  

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
         8

Note that this step requires a downtime of the application, no changes are allowed within the reorganisation.

Starting with Oracle 12.2 you can do this step ONLINE

edited Nov 17 '19 at 17:49

APC

144,005
19
170
281

answered Nov 17 '19 at 10:43

Marmite Bomber

19,886
4
26
53

Thank you. Just one question - Is this 'MOVE' applicable in 10g database? – Naman Kumar Nov 18 '19 at 19:26
Yes, you may find the details in Oracle 10g [documentation](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm#i2081902) – Marmite Bomber Nov 18 '19 at 20:44
1

Thank you. I have tried and it works. Thanks for your help. – Naman Kumar Nov 18 '19 at 21:16

score 0 · Answer 2 · answered Nov 19 '19 at 08:15

0

Free space should not affect your performance that much. Instead, I suspect your statistics may be out of date. Have you tried gathering the statistics, after the delete operation?

answered Nov 19 '19 at 08:15

theoa

71
5

Yes, we did gather stats and even after that, there was not much difference seen in the performance of the fetching query. – Naman Kumar Nov 20 '19 at 11:27

How to remove 'wasted rows' after delete in Oracle SQL database

2 Answers2

Linked