Kolejność kolumn w tabeli

W tym kursie opiszę czy kolejność kolumn w tabeli ma znaczenie. Sprawdzimy jak kolejność ma wpływ na optymalizację bazy danych. Opiszę w jakiś sposób przechowywane są dane w bloku danych w części przeznaczonej na dane. Szczególnie przyjrzymy się jak trzymane są tam nulle. Dokładniej odpowiem na pytania:

Jak wygląda model danych w Oracle?
Jak przechowywane są dane w bloku danych?
Czy kolejność kolumn w tabeli ma znaczenie, w szczególności:
- Różnice w wielkości tabel
- Różnice w czasie dostępu do danych

Ten wpis jest częścią Kurs Oracle SQL

Jak wygląda model danych w Oracle?

Nim przejdę do tematu konkretnego przechowywania danych w bloku danych dla lepszej orientacji opiszę jak zorganizowana jest przestrzeń w bazie danych. Dokładną strukturę bazy danych wraz z opisami przedstawię w innym wpisie, ten opis jest raczej ogólny. W bazie danych mamy przede wszystkim dwie struktury danych, jedna to struktura fizyczna i jedna logiczna.

Struktura fizyczna jest zależna od systemu operacyjnego na jakiej działa baza danych i tak występują w niej dwie podstawowe formy:

Pliki danych
Bloki danych systemu operacyjnego

Plik danych są zbiorem bloków danych. Wielkość bloku danych zależy od systemu operacyjnego i jest porcją danych która jest jednorazowo odczytywana lub zapisywana.

Struktura logiczna w bazie Oracle zawiera idąc od najmniejszego do największego

DATA BLOCK czyli najmniejsza porcja danych w Oracle. Oracle zawsze wczytuje całe bloki danych i na nich wykonuje konkretne operacje. To w nich są przechowywane właściwe dane oraz informacje o nich. Domyślnie jeden data block jest wielkości 8kB.
EXTENT czyli zgrupowana ilość bloków danych przechowująca określoną informacje np. dane tabeli.
SEGMENT czyli zestaw EXTENTÓW. Segment przechowuje dane konkretnej struktury np. tabeli. I tak jeżeli mamy tabelę z indexem to utworzone zostaną dwa segmenty po jednym na tabelę i jednym dla indexu. Jeżeli utworzymy na tabeli partycje to każda partycja będzie osobnym segmentem.
TABLESPACE to najwyższy poziom przechowujący zestawy segmentów. TABLESPACE jest fizycznym plikiem.

Jak przechowywane są dane w bloku danych?

Wiemy już, że blok danych ( ang. “Data block”) jest najmniejszą porcją danych w bazie Oracle. Blok danych nie jest samą porcją danych w sensie danych zapisanych w kolumnach tabeli. Blok danych również ma swoją strukturę i składa się z:

Nagłówka (Block header)
Informacji o tabeli (Table directory)
Informacji o wierszach (Row directory)
Wolnego miejsca (Free space)
Wierszy (Row data)

Z punku widzenia kolejności kolumn w tabeli najbardziej ciekawią nas wiersze (DATA ROW) ponieważ tam trzymane są fizyczne dane. Pamiętajmy, że poza fizycznymi danymi wiersz również posiada dane nagłówkowe.

Sama sekcja danych w wierszu w bloku danych zapisywana jest niezwykle ciekawie. Mianowicie co do zasady Oracle przechowuje dane w kolejności takiej jak zostały utworzone w tabeli. Jednak występują wyjątki i pewne ograniczenia.

Przy tworzeniu tabeli CREATE TABLE kolumny o typie LONG zapisywane są fizycznie na końcu.
Każda dodana kolumna do istniejącej tabeli zapisywana jest na końcu.
W jednym wierszu tabela przechowuje do 256 kolumn.
NULL oznaczony jest jako brak danych.
- Jeżeli wartość NULL zawiera się między kolumnami z danymi zapisywany jest jako jeden bajt.
- Jeżeli NULL występuje na końcu wiersza nie jest w ogóle zapisywany. Baza danych kończy zapisywanie danych na ostatniej kolumnie z wartościami i następnie wpisuje nagłówek kolejnego wiersza.

Oracle przedstawia mniej więcej tak wiersze z nullami dla tabeli z 4 kolumnami np. ID, IMIE, NAZWISKO, WIEK

ID|IMIE|NAZWISKO|WIEK
1,Jan,Kowalski,5   <-- wszystkie kolumny z danymi
2,Adam,,1          <-- kolumna nazwisko z wartością NULL
3,,,7              <-- kolumny imię i nazwisko z wartościami NULL
3,Piotr            <-- kolumny nazwisko i wiek z wartościami NULL
5                  <-- kolumny imię, nazwisko, wiek z wartościami NULL

Jak widać na powyższym null jest zapisywany po prostu jako znacznik kolejnej kolumny gdy występuje między dwoma kolumnami z wartościami. Jeżeli natomiast NULLe występują na końcu nie są po prostu zapisywane. Patrząc na powyższy przykład widać, że rekordy gdzie nulle są na końcu teoretycznie zajmują mniej miejsca bo są krótsze?

Czy kolejność kolumn w tabeli ma znaczenie?

Aby móc odpowiedzieć na pytanie “Czy kolejność kolumn w tabeli ma znaczenie?” należy przeprowadzić test. Z poprzedniego akapitu wiemy jak zapisywane są kolumny z nullem w bloku danych. Test rozpoczniemy od utworzenia dwóch tabel:

Tabela FOO posiada 21 kolumn gdzie pierwsza z nich jest kolumną ID typu NUMBER i tą kolumnę docelowo wypełnimy danymi oraz 20 kolumn typu VARCHAR2() które pozostaną nullami. Kolumny z nullami są na końcu tabeli.
Tabela BAR posiada 21 kolumn gdzie ostatnią z nich jest kolumną ID typu NUMBER i tą kolumnę docelowo wypełnimy danymi oraz 20 kolumn typu VARCHAR2() które pozostaną nullami. Kolumny z nullami są na początku tabeli.

Aby utworzyć obie tabele należy wykonać poniższy skrypt:

CREATE TABLE FOO (
    ID    NUMBER
  , COL1  VARCHAR2(255)
  , COL2  VARCHAR2(255)
  , COL3  VARCHAR2(255)
  , COL4  VARCHAR2(255)
  , COL5  VARCHAR2(255)
  , COL6  VARCHAR2(255)
  , COL7  VARCHAR2(255)
  , COL8  VARCHAR2(255)
  , COL9  VARCHAR2(255)
  , COL10 VARCHAR2(255)
  , COL11 VARCHAR2(255)
  , COL12 VARCHAR2(255)
  , COL13 VARCHAR2(255)
  , COL14 VARCHAR2(255)
  , COL15 VARCHAR2(255)
  , COL16 VARCHAR2(255)
  , COL17 VARCHAR2(255)
  , COL18 VARCHAR2(255)
  , COL19 VARCHAR2(255)
  , COL20 VARCHAR2(255)  
);

CREATE TABLE BAR (
    COL1  VARCHAR2(255)
  , COL2  VARCHAR2(255)
  , COL3  VARCHAR2(255)
  , COL4  VARCHAR2(255)
  , COL5  VARCHAR2(255)
  , COL6  VARCHAR2(255)
  , COL7  VARCHAR2(255)
  , COL8  VARCHAR2(255)
  , COL9  VARCHAR2(255)
  , COL10 VARCHAR2(255)
  , COL11 VARCHAR2(255)
  , COL12 VARCHAR2(255)
  , COL13 VARCHAR2(255)
  , COL14 VARCHAR2(255)
  , COL15 VARCHAR2(255)
  , COL16 VARCHAR2(255)
  , COL17 VARCHAR2(255)
  , COL18 VARCHAR2(255)
  , COL19 VARCHAR2(255)
  , COL20 VARCHAR2(255) 
  , ID    NUMBER
);

Następnie obie tabele wypełnimy danymi tak aby uzupełnić dane tylko w kolumnach ID. Do tego celu posłuży nam skrypt który doda po 1mln wierszy do każdej tabeli oraz przeliczymy statystyki dla obu tabel skryptem:

BEGIN
  FOR I IN 0 .. 1000000
  LOOP
    INSERT INTO FOO (
        ID
    ) VALUES (
        I
    );
  END LOOP;
END;
/

COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','FOO');

BEGIN
  FOR I IN 0 .. 1000000
  LOOP
    INSERT INTO BAR (
        ID
    ) VALUES (
        I
    );
  END LOOP;
END;
/

COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','BAR');

Różnica w wielkości tabel

Skoro mamy już przygotowane tabele oraz wypełniliśmy je danymi spójrzmy czy ich wielkość się różni przy pomocy zapytania:

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 = 'FOO') AS FOO_TABLE_SEGEMNT_SIZE
   , (SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = 'FOO') AS FOO_TABLE_EXTENTS_SIZE
   , (SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = 'BAR') AS BAR_TABLE_SEGEMNT_SIZE
   , (SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = 'BAR') AS BAR_TABLE_EXTENTS_SIZE
 FROM ALL_TABLES 
 WHERE 1=1
   AND TABLE_NAME IN ('FOO', 'BAR');

Oraz wynik powyższego zapytania:

Czy kolejność kolumn ma znaczenie — Wielkość tabel FOO i BAR

Powyższy wynik pokazuje nam, że tabela BAR jest ok 2.5 raza większa od tabeli FOO w przeliczeniu na bloki danych. Dzieje się tak ponieważ większość zapisanych danych w tabeli BAR to znaczniki, że kolumna od COL1 do COL20 są wypełnione nullami. W blokach danych tabeli FOO mamy głównie dane z kolumny ID a wartości NULL są pomijane przy zapisie ponieważ występują na końcu.

Różnica w czasie dostępu do danych

Skoro wiemy, że tabele są różnej wielkości to sprawdźmy, czy szybkość ich przeszukiwania będzie taki sama. Dla przykładu poszukajmy rekordu gdzie ID=500000.

W pierwszej kolejności sprawdźmy koszt przeszukania tabeli FOO poniższym zapytaniem:

SELECT
    *
FROM FOO
WHERE ID = 500000;

Oraz koszt:

Jak widzimy, koszt wykonania tego zapytania wynosi ok. 420

Sprawdźmy więc różnice dla tabeli BAR używając analogicznego zapytania:

SELECT
    *
FROM BAR
WHERE ID = 500000;

Oraz plan:

Na powyższym planie widzimy koszt na poziomie ok. 1122. Widzimy, że koszt wzrósł 2.5 raza czyli mniej więcej tyle samo o ile wzrosła wielkość tabeli.

Dzieje się tak ponieważ w obu planach zapytania metoda dostępu do danych to FULL TABLE SCAN (na tabelach nie mamy indexów) czyli baza czyta po kolei kolejne bloki danych. Skoro tabela BAR jest 2.5 krotnie większa to i ilość operacja musi być ok 2.5 raza większa.

Podsumowanie

Struktury danych tabel idąc od największej do samych danych to: tablespace -> segment -> extent -> data block
Data block składa się z: nagłówka, informacji o tabeli i wierszach, wolnego miejsca i wierszy
Jeżeli ostatnia kolumna jest nullem to nie zajmuje w ogóle miejsca w bazie danych
Jeżeli kolumna z nullem jest pomiędzy kolumnami z danymi to zajmuje jeden bajt.
Kolejność kolumn ma znaczenie ponieważ można w ten sposób zaoszczędzić miejsce na dyskach oraz przyspieszyć wykonywanie się zapytań. W praktyce jednak optymalizacja ta nie ma zbyt wielkiego znaczenia ponieważ stosunek pustych kolumn do ilości danych jest znikomy przez co różnice w wielkości tabel będą niewielkie. Co więcej najczęściej puste są nowe kolumny które dodajemy później czyli na końcu