High Water Mark (HWM)

Generalmente, cuando se crea una tabla o un índice... se crea un segmento asociado al objeto. El segmento se crea con determinados bloques del data file, pero muy poco espacio es destinado para nuestro uso. Cuando los datos van llenando los bloques asignados al segmento, se van alocando más bloques para que usemos.
A medida que los bloques van siendo alocados, la HWM se posiciona en el último bloque para mostrarnos la cantidad total de bloques alocados hasta el momento y estaban disponibles para ser usados.
Un error común es pensar que a medida que eliminamos datos de los bloques, la HWM se refresca para mostrarnos que estamos utilizando menos bloques; pero la realidad es que la HWM se mantiene siempre apuntando al último bloque alocado.
Sabemos que cuando realizamos un Full Scan, son leídos secuencialmente todos los bloques de la tabla hasta la HWM, por lo cual, debemos tener en claro que aunque eliminemos datos de una tabla, ese espacio "vacío" se va a seguir leyendo, lo cual trae como consecuencia la lectura de bloques innecesarios.

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE emp
2  (id NUMBER , sexo VARCHAR2(1 ) ;

Table created.

SQL_9iR2> INSERT INTO emp
2  SELECT level , 'M'
3  FROM dual
4  CONNECT BY level <= 800000 ; 

800000 rows created.  

SQL_9iR2> INSERT INTO emp
2  SELECT level , 'F'
3  FROM dual
4  CONNECT BY level <= 200000 ; 

200000 rows created.  

SQL_9iR2> ANALYZE TABLE emp COMPUTE STATISTICS ;

Table analyzed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT sexo
2  FROM emp
3  WHERE id = 900000 ;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=10000 Bytes=60000)
1   0   TABLE ACCESS (FULL) OF 'EMP' (Cost=160 Card=10000 Bytes=60000)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   1655  consistent gets
      0  physical reads
      0  redo size
    213  bytes sent via SQL*Net to client
    363  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL_9iR2> DELETE FROM emp
2  WHERE sexo = 'M' ;

800000 rows deleted.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT sexo
2  FROM emp
3  WHERE id = 900000 ;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=10000 Bytes=60000)
1   0   TABLE ACCESS (FULL) OF 'EMP' (Cost=160 Card=10000 Bytes=60000)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   1655  consistent gets
    419  physical reads
      0  redo size
    213  bytes sent via SQL*Net to client
    363  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

Como pudimos ver, aunque hayamos eliminados datos de la tabla, seguimos leyendo la misma cantidad de bloques que antes porque la HWM no se refrescó y sigue apuntando al último bloque alocado en el segmento.
Para solucionar éste problema, podemos realizar...

1) TRUNCATE ...
- Si la tabla esta vacia podemos hacer un TRUNCATE para resetear la HWM. Sino, podemos hacer un export de los datos, luego truncar la tabla y realizar un import.
2) ALTER TABLE ... MOVE
- De ésta manera reorganizamos la tabla. Tener en cuenta que luego de ejecutar el ALTER, hay que hacer un REBUILD de todos los índices de la tabla!
3) Dropear y recrear el objeto (export/import)
4) ALTER TABLE ... SHRINK SPACE | SHRINK SPACE COMPACT
- Para 10g en adelante.

Apliquemos a nuestro ejemplo el punto 2...

SQL_9iR2> ALTER TABLE emp MOVE ;

Table altered.

SQL_9iR2> ANALYZE TABLE emp COMPUTE STATISTICS ;

Table analyzed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT sexo
2  FROM emp
3  WHERE id = 900000 ;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=2000 Bytes=12000)
1   0   TABLE ACCESS (FULL) OF 'EMP' (Cost=33 Card=2000 Bytes=12000)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    335  consistent gets
      0  physical reads
      0  redo size
    213  bytes sent via SQL*Net to client
    363  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

Como vemos, la HWM se refresco y ahora estamos leyendo sólo los bloques que contienen nuestros datos.