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