--
-- Load some records
--

scott@9i > @emp_loopinsert

PL/SQL procedure successfully completed.

scott@9i > get emp_loopinsert
  1   begin
  2   for mag in 1..15 loop
  3       insert into emp (select * from emp);
  4   end loop;
  5   commit;
  6*  end;
scott@9i >
scott@9i > select count(*) from emp;

  COUNT(*)
----------
    458752


--
-- index and gather stats
--
scott@9i > create index myindex on emp(empno);

Index created.

scott@9i > analyze table emp compute statistics;

Table analyzed.

scott@9i > analyze index myindex compute statistics;

Index analyzed.

--
-- lets check how many extents are allocated and the size_in_MB it took
--

scott@9i > get max_ext_reach
  1  select  segment_name "name",
  2          segment_type,
  3          max_extents "maximum allowed",
  4          extents "current"
  5  from    user_segments
  6* where   segment_name='EMP';
  7  .
scott@9i > get size
  1* Select segment_name,bytes /(1024*1024) from user_segments where segment_name='EMP';
scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         22

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       22

--
--Enable trace
--
scott@9i > set autotrace traceonly exp stat
scott@9i > set timing on

--
-- Lets do the delete
--

scott@9i > delete from emp where deptno=10;

98304 rows deleted.

Elapsed: 00:00:26.91

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=135 Card=152917 Byte
          s=764585)

   1    0   DELETE OF 'EMP'
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=135 Card=152917 Bytes
          =764585)





Statistics
----------------------------------------------------------
        296  recursive calls
     299679  db block gets
       1536  consistent gets
       1456  physical reads
   47245832  redo size
       1019  bytes sent via SQL*Net to client
       1044  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      98304  rows processed


scott@9i > set autotrace off
scott@9i > set time off


--
-- check the size/extents again.
-- thoufh 98304 rows are deleted, no space/extents is released
--
scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       22

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         22

--
-- So the table has less data now.
-- But if you query the table, you will search alll the 22 extents, even though 
-- some have no data in it.
--
-- NOw use CTAS
-- Truncate the table
-- check the size/extents
-- all space is released.

scott@9i > create table another_emp nologging as select * from emp where deptno !=20;

Table created.

scott@9i > set timing on
scott@9i > truncate table emp;

Table truncated.

Elapsed: 00:00:02.18
scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                        1

Elapsed: 00:00:00.07
scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645          1

Elapsed: 00:00:00.07


--
-- insert your data back.
--

  1* insert /*+ append */ into emp (select * from another_emp)
scott@9i > /

196608 rows created.

Elapsed: 00:00:10.62

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       10

Elapsed: 00:00:00.07
scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         10

Elapsed: 00:00:00.07

---------------------------------------------------------------------------------------------------
--
-- Since Truncate deletes all the data, it may not be an option (if you cannot use CTAS).
-- so the least we can do is ( if there is a huge delete)
-- 1. if you are deleting all data , truncate /drop the table
-- 2. if you are deleting only selective data, use CTAS as shown above.
-- 3. If you delete regularly and you want reset hwm alter table move will also help
      you can see here again, after delete no space is released.
      so move the table to the same tablespace it is.
      this will release the space
      You also need to move the indexes seperatly
      after all done, make sure you gather the table/index stats again!.
----------------------------------------------------------------------------------------------------
scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       10

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         10

scott@9i > delete from emp;

196608 rows deleted.

scott@9i > commit;

Commit complete.

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       10

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         10

scott@9i > alter table emp move tablespace users;

Table altered.

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                        1

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645          1