Oracle SQL - Alter table - fizyczna reorganizacja danych z tabeli

Instancja bazy danych Oracle w dużej mierze sama troszczy się o to, żeby dane były poukładane na dyskach w sposób co najmniej logiczny. Jednak defragmentacja tabeli w Oracle jest jak najbardziej możliwa. Najczęściej pojawia się, gdy korzystamy z opcji /*+ APPEND */ przy insertach do bazy (tzw. direct load).

(append zapewnia, że Oracle używa przy insert-ach wyłącznie pustych bloków - które w zasadzie zdarzają się wyłącznie na samym końcu)

Jest to sygnał, żeby instancja na szybko odnalazła ostatnie wpisy danej tabeli. I ustawiła się do pisania tuż za nimi. W praktyce będzie to pierwsze miejsce za HWM (High Water Mark - Znacznik wysokiej wody). Znacznik ten zostanie przesunięty dalej w "prawo", dalej wielkość tabeli z całą pewnością wzrośnie.

Jeśli do bazy inserty idą zawsze w trybie append a pojawiają się częste delete-y to wielkość tabeli nieuchronnie będzie wzrastać. Natomiast ilość danych w niej zawarta nie koniecznie

Aby się przed tym uchronić należy co jakiś czas sprawdzać czy przypadkiem nie można by było pewnych tabel zmniejszyć. Na początek można sprawdzić jak wyglądają wielkości tabel z widoku user_extents.

select segment_name, sum(bytes) as tablesize
 from user_extents
 where segment_type = 'TABLE'
 group by segment_name
 order by tablesize desc;

alter table ... move

Jak już zlokalizujemy nieprzyzwoicie dużą tabelę trzeba zastosować do nie "move". To samo polecenie które stosuje się do przenoszenia tabeli pomiędzy tablespace-mi.

alter table xyz move

Niestety move ma tą wadę [zaletę], że przenosząc dane reorganizuje strukturę rekordów więc wszystkie kolumny z wartością rowid - charakterystyczną dla kolumn - indeksowych staną się nieaktualne. Co za tym idzie indeksy będą miały statusy różne niż VALID (UNUSABLE, INVALID)

Aby to obejść należy sprawdzić jakie istnieją indeksy na danej tabeli.

select * from user_indexes where table_name = 'XYZ';

I przebudować je wszystkie po kolei

alter index xyz_pk rebuild;
alter index xyz_fk1 rebuild;
...

Osobiście preferuję automatyczne generowanie skryptów "na potem". Np.

select 'alter index '||index_name||' rebuild;' from user_indexes where table_name = 'MY_TABLE';

Wynik wykonania można przechowywać w jakimś pliku .sql

Można oczywiście stosować opcje drop a później create index. Niczym jednak rebuild im nie ustępuje. Poza tym jest sporo szybszy.

create table as select ... (CTAS)

Całkowicie innym rozwiązaniem jest tworzenie nowej tabeli na podstawie starej a później podmiana nazw. Nie jest to jednak wcale łatwiejsze. Usunięcie starej tabeli musi wiązać się z troską o klucze na tej tabeli a to wcale nie musi być łatwe.

dbms_redefinition

Jeszcze ciekawszym pomysłem jest użycie Oracle-owego pakietu dbms_redefinition, który pozwala na podobne sztuczki jak CTAS

alter table shrink space

To jest chyba najlepsze rozwiązanie tego problemu. Shrink space zachowuje się podobnie do move ale potrafi też zwalniać nieużywane extenty

alter table xyz enable row movement;
alter table xyz shrink space;

Data pump (expdp, impdp)

Nie spotkałem się w praktyce z stosowaniem tego już ostatniego mechanizmu do oszczędzania miejsca. Z tego co czytałem nie jest on zbyt popularny.

2010.03.28 11:48:35.