Фрагментация таблиц в Oracle

ОГЛАВЛЕНИЕ

Следует понимать, что фрагментация таблиц отлична от файловой фрагментации. Когда выполняется серия операций DML над таблицей, таблица фрагментируется, потому что DML не освобождает свободное пространство до HWM.

HWM - это индикатор использования блоков (USED BLOCKS) в базе данных. Блоки идущие до линии HWM - используемые блоки и содержат данные. Эти данные могут быть удалены. Oracle знает какие блоки до HWM не содержат данных, он читает блоки выше HWM, когда выполняет полное сканирование таблицы.

DDL предложение всегда сбрасывает HWM.

Как найти фрагментацию таблицы?

SQL> SELECT COUNT(*) FROM BIG1;
1000000 rows selected.

SQL> DELETE FROM BIG1 WHERE ROWNUM <= 300000;
300000 rows deleted.

SQL> COMMIT;
Commit complete.

SQL> UPDATE BIG1 SET OBJECT_ID = 0 WHERE ROWNUM <=350000;
342226 rows updated.

SQL> COMMIT;
Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG1');
PL/SQL procedure successfully completed.

Размер таблицы (с фрагментацией)

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 72952KB

Реальные данные:

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 30604.2KB 
Итог = 72952 - 30604 = 42348 Kb используется без толку.

Разница между двумя значениями почти 60% и Pctfree 10% (по-умолчанию), в итоге, 50% пространства не используется и простаивает, потому что не содержит данных.

Как сбросить HWM/убрать фрагментацию?

Для этого потребуется реорганизовать фрагментированную таблицу. Есть четыре опции для реорганизации фрагментированных таблиц:

  1. alter table ... move + rebuild indexes
  2. export / truncate / import
  3. create table as select (CTAS)
  4. dbms_redefinition