W tym kursie zobaczymy czym różni się operacja DELETE od TRUNCATE oraz czym jest w Oracle High Water Mark. Zobaczymy również jaki wpływ na plan zapytania oraz szybkość wykonywania zapytania mają powyższe operacje i co możemy zrobić aby sterować zachowaniem bazy usuwając dane.

Ten kurs jest częścią Kurs Oracle SQL

Wszystko pokażę na przykładach i sprawdzeniu jak zachowuje się baza Oracle

Przygotowanie danych

Na samym początku pokaże jak zachowuje się baza przy użyciu polecenia DELETE oraz TRUNCATE. W tym celu pierw przygotuję bazę pod testy. Jako, że będziemy głównie usuwać dane to wystarczy przygotwać dwie dowolne tabele wykonując poniższy skrypt:

CREATE TABLE TEST_TABLE_DELETE (
    FOO VARCHAR2(250 CHAR)
  , BAR NUMBER
);

CREATE TABLE TEST_TABLE_TRUNCATE (
    FOO VARCHAR2(250 CHAR)
  , BAR NUMBER
);

Następnie wypełnimy powyższe tabele takimi samymi danymi przy użyciu poniższego skryptu PL/SQL:

BEGIN
  FOR I IN 0 .. 100000
  LOOP
    INSERT INTO TEST_TABLE_DELETE (
        FOO
      , BAR
    ) VALUES (
        'TESTOWY REKORD NR' || I
      , I
    );
    
    INSERT INTO TEST_TABLE_TRUNCATE (
        FOO
      , BAR
    ) VALUES (
        'TESTOWY REKORD NR' || I
      , I
    );
  END LOOP;
END;
/
COMMIT;

Oraz przeliczymy statystyki dla powyższych tabel:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE_DELETE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE_TRUNCATE');

W tym momencie mamy przygotowaną bazę a dokładniej dwie tabele z 100000 rekordów w każdej z nich.

Działanie DELETE i TRUNCATE

Na samym początku porównajmy dwie operacje: DELETE i TRUNCATE

Mając dwie takie same tabele zobaczmy jak wyglądają plany zapytania dla zwykłego SELECTa:

SELECT * FROM TEST_TABLE_DELETE;
SELECT * FROM TEST_TABLE_TRUNCATE;

oraz plany:

Jak widzimy oba plany są identyczne co nie dziwi bo i zawartość obu tabel jest identyczna.

Wyczyśćmy więc obie tabele. Jedną z nich przy pomocy DELETE i drugą przy pomocy polecenia TRUNCATE:

DELETE FROM TEST_TABLE_DELETE;
TRUNCATE TABLE TEST_TABLE_TRUNCATE;

Ponownie przeliczmy statystyki:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST_TABLE_DELETE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST_TABLE_TRUNCATE');

I zobaczmy ponownie plany zapytania SELECT dla pustych tabel:

SELECT * FROM TEST_TABLE_DELETE;
SELECT * FROM TEST_TABLE_TRUNCATE;

Jak widzimy teraz oba plany różnią się znacząco. Pomimo, że każda z tabel zawiera po usunięciu taką samą liczbę rekordów równą 0.

Plan dla tabeli dla której wykonano operację DELETE w zasadzie nie różni się od planu przed usunięciem danych. Zupełnie inną sytuację mamy w planie zapytania tabeli na której wykonano operację TRUNCATE. Tu plan zapytania zmniejszył się znacząco.

Różnica w planie zapytania obu tabel jest wynikiem znacznika występującego w tabelach Oracle HIGH WATER MARK który opisuję poniżej.

Zobaczmy więc czy na pewno obie tabele są tej samej wielkości sprawdzając extenty tabel używając zapytania:

SELECT
    *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME IN ('TEST_TABLE_DELETE', 'TEST_TABLE_TRUNCATE');

Oraz wynik:

Pomimo, że obie tabele zawierają dokładnie 0 rekordów to każda z nich ma inny rozmiar. W szczególności tabela TEST_TABLE_DELETE ma znacznie większy rozmiar niż tabela poddana operacji TRUNCATE.

High Water Mark

W dużym uproszczeniu High Water Mark lub często używany skrótowo HWM to znacznik mówiący do którego bloku mogą występować dane w tabeli. Prześledźmy poniższą sekwencję aby zobaczyć jak zachowuje się HWM w czasie życia tabeli.

Na samym początku gdy utworzymy tabelę jej HWM wskazuje na początek tabeli.

Zobaczmy jak zmieni się HWM gdy dodamy do tabeli rekordy.

Po dodaniu rekordów HWM przesuwa się do ostatniego bloku wypełnionych danych.

Zobaczmy gdzie znajdował się będzie HWM gdy usuniemy połowę danych przy pomocy operacji delete:

Po usunięciu danych przy pomocy operacji DELETE znacznik HWM pozostaje niezmieniony.

W odróżnieniu od operacji DELETE operacja TRUNCATE ustawia HWM na początku tabeli. Jednak w przypadku operacji TRUNCATE nie ma możliwości częściowego usunięcia danych z tabeli. Dlatego właśnie operacja DELETE nie zmienia HWM ponieważ baza nie wie które bloki zawierają jeszcze dane a które są puste.

Sprawdzenie poziomu HWM

Aby określić poziom HWM zaproponowane zapytanie ze strony Burleson Consulting:

select
    a.owner,
    a.table_name,
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
from
    dba_tables a,
    dba_segments b
where
    a.table_name=b.segment_name
    and a.owner=b.owner
    and a.table_name IN ('TEST_TABLE_DELETE', 'TEST_TABLE_TRUNCATE')
order by 1,2;

oraz wynik dla naszych tabel po ich wyczszczeniu:

Jak widać HWM po DELETE jest na innym poziomie względem operacji TRUNCATE

Wpływ HWM na plan zapytania

HWM jest bardzo ważnym znacznikiem wpływającym na czas wykonywania zapytania. W powyższym przykładnie widzieliśmy, że pomimo usunięcia danych z tabeli DELETE i pozostawieniu HWM na pierwotnym poziomie plan zapytania w zasadzie się nie zmienił. Dzieje się tak ponieważ HWM określa miejsce do którego baza danych wykonuje operacje FULL SCAN na tabeli. W związku z tym usunięcie danych z tabeli przy pomocy operacji DELETE nie spowoduje przyspieszenia zapytania gdy wykonujemy zapytanie bezpośrednio na tabeli (bez indexów).

Aby zaktualizować HWM należy ana tabeli wykonać operację TRUNCATE. Nie jest to jednak zawsze możliwe, dlatego też Oracle udostępnia narzędzie dzięki którym możemy zmienić HWM. Aby zaktualizować HWM w pierwszej kolejności musimy udostępnić możliwość przenoszenia rekordów w tabeli używając polecenia:

ALTER TABLE TEST_TABLE_DELETE ENABLE ROW MOVEMENT;

Następnie możemy zmniejszyć przestrzeń tabeli a co za tym idzie zmienić położenie HWM używając polecenia:

ALTER TABLE TEST_TABLE_DELETE SHRINK SPACE;

Po wykonaniu powyższych operacji i przeliczeniu statystyk, HWM w tabeli zmieni się:

Zmieni się również plan zapytania zmniejszając znacznie koszt.

Uwaga: opcja row movement pozwala na przenoszenie rekordów między blokami co skutkuje między innymi zmianę ROWID rekordów.

Podsumowanie

  • High Water Mark w Oracle jest określeniem poziomu do którego występować mogą dane w tabeli.
  • Operacja TRUNCATE zmienia HWM
  • Operacja DELETE nie zmienia HWM
  • HWM ma istotny wpływ na prędkość wykonywania zapytania ponieważ do jego poziomu baza wykonuje FULL SCAN
  • Aby zaktualizować HWM w tabeli należy umożliwić przenoszenie rekordów a następnie wykonać SHRINK na tabeli.
  • Jeśli chcesz być na bieżąco obserwuj mój profil na: Facebooku

  • Jeżeli chcesz znaleźć więcej kursów dla średniozaawansowanych oraz poznać zasady optymalizacji zapytań zapoznaj się z pozostałymi kursami, sprawdź: Kurs Oracle SQL
  • Jeżeli chcesz poznać podstawy baz danych i kurs dla początkujących w SQL odwiedź Kurs SQL

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *