Table fragmentation

Table frgmentation is commonly known, but nevertheless I'll drop here a line.
Assume we got filled table and then we delete some rows from it...

1. Create table and fill it:

sqlplus> create table scott.tt (id number(5), str varchar2(2000));

Table created.

sqlplus> create sequence scott.tt_seq start with 1;

Sequence created.

sqlplus> insert into scott.tt select scott.tt_seq.nextval, dbms_random.string('U', 2000) from dual connect by level <= 30000;

30000 rows created.

sqlplus> commit;

Commit complete.

2. Show some statistics:

sqlplus> exec dbms_stats.gather_table_stats('scott', 'TT');

PL/SQL procedure successfully completed.

sqlplus> select t.num_rows, s.blocks, s.bytes from dba_segments s, dba_tables t -

> where s.owner=t.owner and s.segment_name=t.table_name and -

> s.owner = 'scott' and segment_name='TT';

  NUM_ROWS     BLOCKS      BYTES

---------- ---------- ----------

     29989      10240   83886080

sqlplus> set autotrace traceonly

sqlplus> select * from scott.tt;

30000 rows selected.



Execution Plan

----------------------------------------------------------

Plan hash value: 481005422

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 29989 |    57M|  2218   (1)| 00:00:27 |

|   1 |  TABLE ACCESS FULL| TT   | 29989 |    57M|  2218   (1)| 00:00:27 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      12108  consistent gets

       5152  physical reads

          0  redo size

   60577626  bytes sent via SQL*Net to client

      14239  bytes received via SQL*Net from client

       2001  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      30000  rows processed

3. Now let's delete some rows from table

sqlplus> set autotrace off

sqlplus> delete from scott.tt where id < 30000;

29999 rows deleted.

sqlplus> commit;

Commit complete.

4. Show stats again

sqlplus> exec dbms_stats.gather_table_stats('scott', 'TT');

PL/SQL procedure successfully completed.

sqlplus> select t.num_rows, s.blocks, s.bytes from dba_segments s, dba_tables t -

> where s.owner=t.owner and s.segment_name=t.table_name and -

> s.owner = 'scott' and segment_name='TT';

  NUM_ROWS     BLOCKS      BYTES

---------- ---------- ----------

         1      10240   83886080

sqlplus> set autotrace traceonly

sqlplus> select * from scott.tt;



Execution Plan

----------------------------------------------------------

Plan hash value: 481005422



--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |  2004 |  2217   (1)| 00:00:27 |

|   1 |  TABLE ACCESS FULL| TT   |     1 |  2004 |  2217   (1)| 00:00:27 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      10108  consistent gets

       6639  physical reads

          0  redo size

       2258  bytes sent via SQL*Net to client

        246  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

There's 1 row in the table only, but nevetheless we made 10108 consistent reads. Why?
Because of:
1. HWM grows while insert data into table, but never falls down after delete operation (except truncate).
2. Oracle reads all the blocks in a table up to HWM while doing FTS, despite whether these blocks contains data or not.

What we should do? There're many thins to do about it. For example "shrink".

sqlplus> alter table scott.tt shrink space;

alter table scott.tt shrink space

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled



sqlplus> alter table scott.tt enable row movement;

Table altered.

sqlplus> alter table scott.tt shrink space;

Table altered.

Statistics:

sqlplus>  exec dbms_stats.gather_table_stats('scott', 'TT');

PL/SQL procedure successfully completed.

sqlplus> select * from scott.tt;



Execution Plan

----------------------------------------------------------

Plan hash value: 481005422



--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |  2004 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TT   |     1 |  2004 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------



Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       2256  bytes sent via SQL*Net to client

        246  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



sqlplus> set autotrace off

sqlplus> select t.num_rows, s.blocks, s.bytes from dba_segments s, dba_tables t

  2  where s.owner=t.owner and s.segment_name=t.table_name and

  3  s.owner = 'scott' and segment_name='TT';

  NUM_ROWS     BLOCKS      BYTES

---------- ---------- ----------

         1          8      65536

Now we did only 4 block reads.


Along with "shrink" there're other methods:
1. CTAS
2. alter table ... move
3. exp, truncate, imp
4. dbms_redefinition