title: Oracle 12c: Wasted Disk Space and Performance
body: <p>The nature of my application involves daily deleting and bulk inserting of large datasets into an Oracle 12c database. My tables are interval-partitioned by a date field and partitioned-indexed. I use a stored procedure to gather statistics for the affected partitions after each run. Lately, I found that the runs have been slowing down considerably and was wondering if this was due to the increasing size of the database.</p>
  
  <p>I have searched for how to calculate the total disk space that my tables use and usually arrive at this:</p>
  
  <pre><code>select sum(bytes)/1024/1024/1024 
  from dba_segments 
  where owner='SCHEMA' and segment_name in ('TABLE_A', 'TABLE_B');
  </code></pre>
  
  <p>However, the numbers were huge and do not reflect the actual data volume used. When we exported the tables for restoration to another database, the file was much smaller than that query suggests. I dug deeper and arrived at this query instead:</p>
  
  <pre><code>select partition_name, 
         blocks*8/1024 size_m, 
         num_rows*avg_row_len/1024/1024 occ_m, 
         blocks*8/1024 - num_rows*avg_row_len/1024/1024 wast_m 
  from dba_tab_partitions 
  where table_name='TABLE_A'; 
  </code></pre>
  
  <p>This query suggests that there is a "wasted" space concept where after performing bulk inserts and deleting the data before it is replaced again, the space used is not reclaimed.</p>
  
  <p>Thus I have the following questions:</p>
  
  <ol>
  <li>Does the "wasted" space contribute to performance degradation when I
  perform <code>delete from table where ..</code>?</li>
  <li>Is there a difference between
  performing a <code>delete from table where ..</code> as compared to dropping
  the partitions with regard to "wasted" space?</li>
  <li>Is performing table reorganization / defragmentation on a regular basis to reclaim table space a recommended practice?</li>
  </ol>