Фрагментация таблиц в 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 

Читайте также:
  • Управление табличными пространствами в Oracle Database
    Default Permanent Tablespace Переименование табличного пространстваТабличное пространство SYSAUX Составное табличное пространство TempDefault Permanent TablespaceOracle 9i ввел понятие временного табличного пространства по умолчанию (default temporary tablespace), что позволило предотвратить случайн...
  • Таблицы только для чтения в Oracle 11g Release 1
    ALTER TABLE table_name READ ONLY;ALTER TABLE table_name READ WRITE;Следующий скрипт создает таблицу, добавляет в нее несколько строк, затем устанавиливает таблицу в режим "только для чтения".CREATE TABLE ro_tab (  id NUMBER);INSERT INTO ro_tab VALUES (1);INSERT INTO ro_tab VALUES (2)...
  • Ручное создание базы данных в Oracle 11g
    Процедура создания практически не отличается от предыдущих версий - 9i и 10g. В создаваемой базе данных будем использовать следующие опции:OMF (Oracle Managed File) для файлов данных, файлов журналов повторного выполнения и управляющих файлов. FRA (Flash Recovery Area) для архивных журналов или резе...
  • Управление SQL-планами в Oracle Database 11g
    Невозможность гарантировать, что все изменения плана всегда будут в лучшую сторону, привела некоторых заказчиков к тому, чтобы закрепить свои планы выполнения (хранимые планы) или блокировать статистику оптимизатора. Однако, если поступать подобным образом, мы лишаем себя возможности когда-либо испо...
  • Производительность PL/SQL
    Native compilation – это не совсем новая возможность, однако теперь нет «узких» мест её использования, например, установка компилятора C (Oracle назвал эту замечательную возможность "Real Native Compilation"). Кроме того, новый тип данных simple_integer делает выполнен...