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:
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".
Along with "shrink" there're other methods:
1. CTAS
2. alter table ... move
3. exp, truncate, imp
4. dbms_redefinition
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 tablesqlplus> 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
