Фрагментация таблиц в Oracle - alter table ... move + rebuild indexes
ОГЛАВЛЕНИЕ
Страница 2 из 5
Опция 1 "alter table ... move + rebuild indexes"
SQL> ALTER TABLE BIG1 MOVE;
TABLE altered.
SQL> SELECT STATUS, INDEX_NAME FROM USER_INDEXES
2 WHERE TABLE_NAME = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX
SQL> ALTER INDEX BIGIDX REBUILD;
Index altered.
SQL> SELECT STATUS, INDEX_NAME FROM USER_INDEXES
2 WHERE TABLE_NAME = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX
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 38224KB
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 30727.37KB