W tym kursie Oracle SQL opiszę zachowanie bazy Oracle przy zakładaniu DEFAULT na kolumnę która przyjmuje wartości NULL oraz specyficzne zachowanie DEFAULT z kolumną która jest typu NOT NULL(nie przyjmuje wartości null). W tym kursie opiszę skrótowo również podstawy SQL dotyczące DEFAULT.

Ten wpis jest częścią Kurs SQL Oracle

Klauzula DEFAULT w SQL służy do określenia wartości domyślnej dla nowej kolumny lub już istniejącej kolumny. Baza danych Oracle przypisuje tę wartość do kolumny w momencie wykonania operacji INSERT gdy pominięto wartość dla kolumny ze zdefiniowaną wartością DEFAULT.

Działanie DEFAULT

Na początku przedstawię skrótowo działanie klauzuli DEFAULT na kolumnach w tabeli. Jako, że najlepiej uczyć się na przykładach to stwórzmy testową tabelę:

CREATE TABLE TEST_TABLE (
    FOO         VARCHAR2(100 CHAR)
  , BAR         VARCHAR2(100 CHAR) DEFAULT 'DEFAULT BAR'
  , TEST_NN     VARCHAR2(100 CHAR) DEFAULT 'DEFAULT TEST_NN' NOT NULL
  , TEST_ON     VARCHAR2(100 CHAR) DEFAULT ON NULL 'DEFAULT TEST_ON'
  , TEST_ON_NN  VARCHAR2(100 CHAR) DEFAULT ON NULL 'DEFAULT TEST_ON_NN' NOT NULL
);

Powyżej mamy testową tabelę z 5 kolumnami:

  • FOO, zwykła kolumna typu varchar2() bez default. Posłuży jako benchmark dla pozostałych.
  • BAR, kolumna ze zdefiniowaną wartością DEFAULT, kolumna typu NULL
  • TEST_NN, kolumna ze zdefiniowaną wartością DEFAULT, kolumna typu NOT NULL
  • TEST_ON, kolumna ze zdefiniowaną wartością DEFAULT z klauzula ON NULL, kolumna typu NULL
  • TEST_ON_NN, kolumna ze zdefiniowaną wartością DEFAULT z klauzulą ON NULL, kolumna typu NOT NUL

Skoro mamy już tabelę testową postarajmy się użyć najprostszego INSERTA do tej tabeli oraz zobaczmy wynik dodania rekordu:

INSERT INTO TEST_TABLE(FOO, BAR, TEST_NN, TEST_ON, TEST_ON_NN) VALUES ('A','B', 'C', 'D', 'E');
SELECT * FROM TEST_TABLE;

Oraz wynik:

Wynik prostego INSERT

W sumie nie ma żadnego zaskoczenia. Wartości które podaliśmy w INSERT zostały dodane. Żadna z wartości default nie została dodana ponieważ żadnej z kolumn w trakcie operacji INSERT nie pomineliśmy. Zobaczmy w takim razie co się stanie gdy pominiemy kolumny z klauzulą DEFAUL:

INSERT INTO TEST_TABLE (FOO) VALUES ('ACME');
SELECT * FROM TEST_TABLE;

oraz wynik:

Działanie DEFAULT

W tym przykładzie widzimy zadziałanie klauzuli DEFAULT czyli w momencie INSERT gdy nie podaliśmy wartości baza Oracle podstawiła wartości DEFAULT kolumny (choć nie jest to do końca prawda, ale o tym później). Co się natomiast stanie gdy podamy wartości NULL na kolumnach z DEFAULT?

INSERT INTO TEST_TABLE(FOO, BAR, TEST_NN, TEST_ON, TEST_ON_NN) VALUES (NULL, NULL, NULL, NULL, NULL);

oraz wynik:

Error report -
ORA-01400: cannot insert NULL into ("SYSTEM"."TEST_TABLE"."TEST_NN")

Otrzymamy błąd, próby dodania wartości NULL do kolumny typu NOT NULL. Na co należy zwrócić uwagę to fakt, że w tabeli TEST_TABLE występują dwie kolumny NOT NULL tj. TEST_NN oraz TEST_ON_NN jednak ta ostatnia ma dodatkową klauzulę ON NULL. Klauzula ON NULL podstawia wartości z DEFAULT również w momencie gdy następuje próba INSERT wartości NULL. Sprawdźmy, co się stanie gdy dokonamy powyższego INSERT z pominięciem kolumny TEST_NN tak jak w przykładzie:

INSERT INTO TEST_TABLE(FOO, BAR, TEST_ON, TEST_ON_NN) VALUES (NULL, NULL, NULL, NULL);
SELECT * FROM TEST_TABLE;

oraz wynik:

DEFAULT insert NULLs

A więc widzimy, że do kolumny TEST_ON_NN podstawiona została wartość z DEFAULT pomimo próby dodania wartości NULL. Ciekawym również zachowaniem cechuje się również kolumna BAR w którą wstawiona została wartość NULL. Tak więc należy jeszcze raz zaznaczyć, że Oracle w SQL wyzwoli DEFAULT w sytuacji gdy nie dodamy żadnej wartości w INSERT chyba, że użyjemy dodatkowej klauzuli ON NULL.

Dodanie kolumny NOT NULL do istniejącej tabeli

Często zdarza się, że do istniejącej tabeli chcemy dodać nową kolumnę typu NOT NULL. Operacji takiej nie można po prostu wykonać ponieważ otrzymamy błąd ORA-01758 mówiący, że tabela musi być pusta aby założyć ograniczenie NOT NULL:

ORA-01758: table must be empty to add mandatory (NOT NULL) column

W takim razie co można zrobić aby założyć kolumnę NOT NULL na istniejącą już tabelę wypełnioną danymi? Na pierwszy rzut można dodać kolumnę z dozwoloną wartością NULL, następnie napełnić tą kolumnę danymi a po wypełnieniu kolumny danymi nałożyć ograniczenie NOT NULL. Takie rozwiązanie oczywiście zadziała jednak nie jest optymalne (o tym dlaczego dalej). Innym rozwiązaniem jest utworzenie kolumny z DEFAULT oraz ograniczeniem NOT NULL. Utwórzmy więc dane testowe. W pierwszej kolejności własny tablespace:

CREATE TABLESPACE test_data2
   DATAFILE 'TEST_DEFAULT2.dat' 
     SIZE 1M
     REUSE
     AUTOEXTEND ON NEXT 1M MAXSIZE 200M;

Oraz tabela w tym tablescpae:

CREATE TABLE TEST_TABLE (
     FOO VARCHAR2(500 CHAR)
 ) TABLESPACE test_data2;

Prosta tabela z jedną kolumną, teraz wypełnijmy ją “danymi”:

BEGIN
   FOR I IN 0 .. 10000
   LOOP
     INSERT INTO TEST_TABLE (
         FOO
     ) VALUES (
         NULL
     );
   END LOOP;
 END;
 /

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE');

Mamy już tabelę zawierającą 10 tyś. pustych rekordów. Dodatkowo przeliczyliśmy statystyki. Zobaczmy więc jak przedstawia się wielkość tabeli poniższym zapytaniem:

SELECT
     TABLE_NAME
   , NUM_ROWS
   , BLOCKS
   , AVG_ROW_LEN
   , CHAIN_CNT
   , NUM_ROWS*AVG_ROW_LEN AS TABLE_SIZE
   , (SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = 'TEST_TABLE') AS TABLE_SEGEMNT_SIZE
   , (SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = 'TEST_TABLE') AS TABLE_EXTENTS_SIZE
 FROM ALL_TABLES 
 WHERE 1=1
   AND TABLE_NAME = 'TEST_TABLE';

A więc powyższe zapytanie przedstawia:

  • TABLE_NAME – tabela której dotyczy test
  • NUM_ROWS – ilość rekordów w tabeli
  • BLOCKS – ilość zajmowanych bloków przez tabelę
  • AVG_ROW_LEN – średnia długość rekordu (w bajtach)
  • CHAIN_CNT – ilość rekordów typu chained rows
  • TABLE_SIZE – Szacowana wielkość tabeli jako iloczyn ilości rekordów i średniej długości rekordu
  • TABLE_SEGMENT_SIZE – wielkość segmentów przeznaczonych na tabelę (w bajtach)
  • TABLE_EXTENDS_SIZE – wielkość ekstendów przeznaczonych na tabelę (w bajtach)

Zapytania tego będę używał w w dalszej części do określania rozmiaru tabeli. Przed wykonaniem tego zapytania pamiętajmy też o przeliczeniu statystyk dla tabeli. Zobaczmy więc wynik dla tabeli TEST_TABEL:

TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, CHAIN_CNT, TABLE_SIZE, TABLE_SEGEMNT_SIZE, TABLE_EXTENTS_SIZE
TEST_TABLE    10001   20  0   0   0   196608  196608

Liczba bloków równa 20, średnia wielkość rekordu 0 ponieważ wszystkie rekordy są NULLami i rozmiar na dysku ok 0.2MB.

Na dysku wielkość tablespace wygląda tak jak utworzyliśmy:

tablespcae 1.1M

Table space wielkości 1M co jest zgodny z tym co utworzyliśmy.

Gdy zrobimy UPDATE na wszystkich rekordach

Idąc sekwencją utworzenia kolumny NOT NULL z UPDATE wykonajmy kroki i zobaczmy efekt:

ALTER TABLE TEST_TABLE ADD (BAR VARCHAR2(500 CHAR));

UPDATE TEST_TABLE
  SET BAR = 'Lorem Ipsum jest tekstem stosowanym jako przykładowy wypełniacz w przemyśle poligraficznym. Został po raz pierwszy użyty w XV w. przez nieznanego drukarza do wypełnienia tekstem próbnej książki. Pięć wieków później zaczął być używany przemyśle elektronicznym, pozostając praktycznie niezmienionym. Spopularyzował się w latach 60. XX w. wraz z publikacją arkuszy Letrasetu, zawierających fragmenty Lorem Ipsum, a ostatnio z zawierającym różne wersje Lorem Ipsum oprogramowaniem przeznaczonym do realiz';

COMMIT;

ALTER TABLE TEST_TABLE MODIFY (BAR NOT NULL);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE');

Powyższy skrypt dodaje kolumnę BAR do testowej tabeli. Następnie wykonuje update na wszystkich rekordach tabeli wypełniając kolumnę BAR danymi. Na końcu nakłada ograniczenie NOT NULL i przelicza statystyki. Zobaczmy jak teraz przedstawia się wielkość tabeli po przeliczeniu statystyk:

TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, CHAIN_CNT, TABLE_SIZE, TABLE_SEGEMNT_SIZE, TABLE_EXTENTS_SIZE
TEST_TABLE    10001   852 528 0   5280528 7340032 7340032

Patrząc po wyniku widzimy, znaczny wzrost wielkości tabeli jednak jest to zrozumiałe ponieważ wypełniliśmy tabelę dużą ilością znaków. Teraz tabela jest wielkości wg. iloczynu wielkości rekordów i ich ilości ok 5MB natomiast po extendach i segmentach ok 7MB a fizycznie tablespace:

tablespace 8.1M

Jak widzimy tablespcae również się zwiększył do 8M.

Powyższe pokazuje, że update został fizycznie wykonany i wszystkie 10tyś. rekordów zostało zaktualizowanych o nową wartość.

Aby usunąć utworzony tablespace należy wykonać polecenie:

DROP TABLESPACE test_data2 INCLUDING CONTENTS AND DATAFILES;

DEFAULT na kolumnie NOT NULL

Tak jak wspomniałem innym rozwiązaniem jest utworzenie kolumny NOT NULL z klauzulą DEFAULT. Utwórzmy jeszcze raz tabelę testową wraz z dedykowanym tablespace:

CREATE TABLESPACE test_data
   DATAFILE 'TEST_DEFAULT.dat' 
     SIZE 1M
     REUSE
     AUTOEXTEND ON NEXT 1M MAXSIZE 200M;

Utwórzmy tabelę w tym tablespcae:

CREATE TABLE TEST_TABLE (
     FOO VARCHAR2(500 CHAR)
 ) TABLESPACE test_data;

Oraz wypełnijmy danymi tak samo jak w poprzednim przykładzie:

BEGIN
   FOR I IN 0 .. 10000
   LOOP
     INSERT INTO TEST_TABLE (
         FOO
     ) VALUES (
         NULL
     );
   END LOOP;
 END;
 /

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE');

Zobaczmy wielkość tabeli:

TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, CHAIN_CNT, TABLE_SIZE, TABLE_SEGEMNT_SIZE, TABLE_EXTENTS_SIZE
TEST_TABLE    10001   20  0   0   0   196608  196608

Tabela ma dokładnie takie same parametry jak w poprzednim teście, tak samo tablespace:

tablespace 1.1M

Dodajmy teraz kolumnę NOT NULL z DEFAULT:

ALTER TABLE
  TEST_TABLE ADD (BAR VARCHAR2(500 CHAR) DEFAULT
'Lorem Ipsum jest tekstem stosowanym jako przykładowy wypełniacz w przemyśle poligraficznym. Został po raz pierwszy użyty w XV w. przez nieznanego drukarza do wypełnienia tekstem próbnej książki. Pięć wieków później zaczął być używany przemyśle elektronicznym, pozostając praktycznie niezmienionym. Spopularyzował się w latach 60. XX w. wraz z publikacją arkuszy Letrasetu, zawierających fragmenty Lorem Ipsum, a ostatnio z zawierającym różne wersje Lorem Ipsum oprogramowaniem przeznaczonym do realiz'
NOT NULL);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE');

A więc powyższy kod dodaje do tabeli TEST_TABLE kolumnę BAR typu NOT NULL z wartością domyślną 500znaków.

Przeliczmy statystyki i zobaczmy jak zmieniła się wielkość tabeli po dodaniu kolumny:

TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, CHAIN_CNT, TABLE_SIZE, TABLE_SEGEMNT_SIZE, TABLE_EXTENTS_SIZE
TEST_TABLE    10001   20  528 0   5280528 196608  196608

I teraz widzimy nieco dziwną sytuację. Mianowicie wielkość tabeli jako iloczyn zmieniła się na ok 5MB natomiast wielkość extendów oraz segmentów wynosi 0.2MB, a na dysku wielkość tablespace wygląda następująco:

tablespace 1.1M

Widzimy więc, że wprowadzenie DEFAULT na kolumnie NOT NULL nie zmieniło wielkości tabeli fizycznie jednak wynik iloczynu wciąż pokazuje 5MB. Zobaczmy, co pokaże nam SELECT z tej tabeli:

Co się więc stało? Widzimy dane po wykonaniu SELECT, średnia wielkość rekordu wzrosła do 528B natomiast fizycznie wielkość nie została zwiększona. Gdzie więc są te dane?

Odpowiedzią jest mechanizm ORACLE w SQL dla DEFAULT na kolumnach NOT NULL wprowadzony w wersji 11g. Mechanizm w powyższym przypadku nie zmienia fizycznie struktury tabeli tzn. nie aktualizuje rekordów o wartość DEFAULT a trzyma ją z boku i w momencie pobierania danych z tabeli podmienia wartość pustą na tą z DEFAULT.

Korzyści z takiego rozwiązania są przede wszystkim dwie:

Pierwsza którą tu zaobserwowaliśmy to zmniejszenie przestrzeni dyskowej potrzebnej do przechowywania danych w tabeli.

Druga i równie istotna to pominięcie blokowania tabeli poprzez wykonanie operacji UPDATE na całej tabeli. Przed wprowadzeniem tego mechanizmu baza danych Oracle po wprowadzeniu DEFAULT na kolumnie NOT NULL wykonywała update na wszystkich rekordach tabeli. Dla dużych tabel na których często dokonuje się operacji użycie DEFAULT należało zaplanować i wykonać najprawdopodobniej w przerwie działania systemu. Wykonanie update na tabeli która ma miliardy rekordów też trwa trochę czasu 🙂

Kiedy ten mechanizm nie zadziała:

  • Gdy tabela zawiera kolumny typu LOB
  • DEFAULT jest typu CURRVAL lub NEXTVAL
  • Dodawana kolumna jest zaszyfrowana, jest kolumną obiektu lub kolumną tabeli zagnieżdżonej

Podsumowanie

  • Klauzula DEFAULT podstawia dane w INSERT gdy kolumnę pominięto
  • Aby podstawić wartości z DEFAULT do kolumny przy dodaniu NULL należy użyć dodatkowej klauzuli ON NULL
  • Dodanie DEFAULT na kolumnie typu NOT NULL nie zmienia fizycznie tabeli a jedynie zapisuje “z boku” tą informację i w momencie pobierania danych z tabeli podstawia wartość z DEFAULT do kolumny. Dzięki temu oszczędzamy miejsce na dysku oraz nie blokujemy tabeli po wprowdzeniu DEFAULT.
  • Powyższy mechanizm dotyczący DEFAULT na kolumnie NOT NULL wprowadzony został w Oracle 11g

Inne kursy

  • 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 e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *