Delete and Truncate and table size

Question

I tried this query to understand the impact on table size after delete and truncate.

select * from SALES_HISTORY;
Output:
Product     Month       Sales
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000
sony    22-DEC-17   24000

Delete from sales_history:
commit;

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_type='TABLE' and segment_name='SALES_HISTORY';

Output:
segment_name    segment_type   MB
SALES_HISTORY   TABLE         0.0625

Truncate table SALES_HISTORY;

After that, I again try finding size of table, but it seems the same as below. Shouldn't it should be zero if have truncated all of the rows?

Output:
segment_name    segment_type   MB
SALES_HISTORY   TABLE         0.0625

I need to understand the impact on the size of table and watermark after delete and truncate.

Answer 1

If you're on 11g R2 or above, see whether DROP ALL STORAGE does what you want. Have a look at the following example:

SQL> create table test_truncate as select level id, sysdate + level datum
  2  from dual connect by level <= 100000;

Table created.

SQL> select segment_name,segment_type,bytes/1024/1024 MB
  2  from dba_segments
  3  where segment_type='TABLE' and segment_name='TEST_TRUNCATE';

SEGMENT_NAME         SEGMENT_TYPE               MB
-------------------- ------------------ ----------
TEST_TRUNCATE        TABLE                       3

SQL> truncate table test_truncate;

Table truncated.

SQL> select segment_name,segment_type,bytes/1024/1024 MB
  2  from dba_segments
  3  where segment_type='TABLE' and segment_name='TEST_TRUNCATE';

SEGMENT_NAME         SEGMENT_TYPE               MB
-------------------- ------------------ ----------
TEST_TRUNCATE        TABLE                   ,0625

SQL> truncate table test_truncate DROP ALL STORAGE;

Table truncated.

SQL> select segment_name,segment_type,bytes/1024/1024 MB
  2  from dba_segments
  3  where segment_type='TABLE' and segment_name='TEST_TRUNCATE';

no rows selected

SQL>

Answer 2

I wonder you are not including the table structure to count in size. That size what is showed is the table's structure size.

 Tables whole size= Tables structure 
 (Table name and fields (0.0625 mb)) + 
 (records/the 
  data (remaining size in mb)) as per the 
above data u showed

To verify alter the table and delete one column/field

  ALTER TABLE SALES_HISTORY DROP 
  Sales;

 select  
 segment_name,segment_type,
bytes/1024/1024 MB
from dba_segments
where segment_type='TABLE' and 
segment_name='SALES_HISTORY';