Tabela zorganizowana według indeksu (IOT)

Tabela zorganizowana według indeksu (ang. Index-Organized Tables) jest specjalnym typem tabeli która jest zbudowana na strukturze indeksu B-drzewa. Inaczej niż w tabeli każdy nowy rekord jest dodawany do struktury zgodnie z strukturą indexu a nie jak w tabeli na koniec wolnego miejsca.

Ten wpis jest częścią Kurs Oracle SQL

Struktura tabeli zorganizowanej według indeksu

Aby dobrze zrozumieć w jaki sposób baza danych Oracle przechowuje tabele zorganizowane według indeksu przypomnijmy sobie jak w zwykłej tabeli zorganizowane są rekordy.

Standardowa tabela w bazie danych Oracle składa się kolumn oraz wartości zorganizowanych w rekordy. Każdy nowy rekord dodawany jest na koniec tabeli. Nieco więcej o budowie bloku danych tabeli opisałem w tym wpisie.

Zupełnie inną strukturą jest indeks który to przechowuje dane w postaci B-drzewa. W taki sposób, że przechowuje kolumny indexu + rowid do rekordu z tabeli. Dokładną budowę indeksu opisałem w tym wpisie. Dla powyższej tabeli indeks założony na kolumnie ID mógłby mieć strukturę jak poniżej:

Przy takiej strukturze wyszukanie rekordu po ID jest bardzo szybkie. Minusem takiego rozwiązania jest fakt, że wyszukując po ID ale chcąc zapytaniem pobrać dane występujące w tabeli baza danych wykona operację Table access by index rowid. Wykonanie tej operacji jest dodatkowym narzutem co w konsekwencji spowalnia wykonanie całego zapytania.

Można zadać więc pytanie, czy nie można w takim razie do indexu dodać wszystkich kolumn? W zasadzie można, w tej sytuacji będziemy w bazie danych przechowywali tabele z danymi + index który zawierać będzie wszystkie dane z tej tabeli. Wykorzystanie takiego indexu przy wyszukiwaniu po ID ale w zapytaniu wyciągającym wszystkie kolumny będzie bardzo wydajne. Minusem takiego rozwiązania jest fakt, że mamy zdublowane dane oraz spowolniony czas dodawania rekordów ponieważ należy go dodać do indexu i tabeli.

Częściowym rozwiązaniem powyższego problemu są właśnie tabele zorganizowane według indeksu (IOT). Taka tabela nie przechowuje danych w standardowej tabeli a jest zbudowana na strukturze indeksu z wszystkimi kolumnami w indeksie. Dla powyższych danych tabela zorganizowana w indeks wyglądać może jak poniżej:

Jak widzimy, powyższa struktura przechowuje wszystkie dane z tabeli w sposób podobny do indeksu. Tabele zorganizowane według indeksu zapewniają szybszy dostęp do wierszy tabeli za pomocą klucza podstawowego lub prawidłowego prefiksu klucza. Przy wykorzystaniu Index Oriented Table nie występuje tradycyjna tabela a jedynie struktura jak powyżej.

Powyższa struktura ma kilka różnicę względem indeksu oraz samej tabeli.

• IOT nie posiada zapisanego ROWID ponieważ dane nie są przechowywane w tabeli, rowid które można pokazać jest logiczną wartością.
• IOT musi posiadać unikalny klucz główny.
• Dostęp do pojedynczego rekordu odbywa się przy pomocy klucza głównego, w zwykłej tabeli przy pomocy rowid.
• IOT nie może posiadać kolumn typu LONG
• Full Scan i Fast Full Scan zwracają dane w posortowanej kolejności.

Przykład tabela zorganizowana według indeksu

Mając już wiedzę jak zbudowana jest tabela zorganizowana według indeksu (IOT) utwórzmy taką tabelę i zobaczmy jak wygląda w bazie danych.

Utworzenie tabeli IOT odbywa się poprzez dodanie opcji ORGANIZATION INDEX przy tworzeniu tabeli. Utworzenie tabeli może wyglądać jak poniżej:

CREATE TABLE OSOBA (
    ID NUMBER GENERATED AS IDENTITY
  , IMIE VARCHAR2(256 CHAR)
  , NAZWISKO VARCHAR2(256 CHAR)
  
  , CONSTRAINT OSOBA_ID_PK PRIMARY KEY(ID)
) ORGANIZATION INDEX;

Opcja ORGANIZATION INDEX powoduje, że utworzona tabela zostanie zorganizowana według indeksu. Dodajmy do tej tabeli więc 1000 rekordów poniższym skryptem:

BEGIN
  FOR I IN 0 .. 1000
  LOOP
    INSERT INTO OSOBA (
        IMIE
      , NAZWISKO
    ) VALUES (
        'TESTOWE IMIE'
      , 'TESTOWE NAZWISKO' || MOD(I, 3)
    );
  END LOOP;
END;
/
COMMIT;

Oraz przeliczmy statystyki:

EXEC DBMS_STATS.GATHER_TABLE_STATS('ORACLEDEV','OSOBA');

Zobaczmy więc jak wygląda ta tabela w ramach widoku user/all_tables wykonując poniższe zapytanie:

SELECT
    *
FROM USER_TABLES
WHERE TABLE_NAME = 'OSOBA';

oraz wynik:

Na wstępie widać, że tablespace_name jest nullem co oznacza, że w tabeli nie ma danych? Tak naprawdę dane są ale nie w tabeli rozumianej jako tabela ale w indexie o czym za chwilę. Ciekawa jest też wartość null w kolumnie IOT_NAME która wskazuje na tabelę z danymi w przypadku gdy taką tabelę zdefiniujemy.

Skoro danych nie ma w tabeli to zobaczmy jak prezentuje się widok USER_INEDXES wykonując poniższe zapytanie:

SELECT
    *
FROM USER_INDEXES
WHERE TABLE_NAME = 'OSOBA';

oraz wynik:

Tu z kolei widzimy, że utworzony został index OSOBA_ID_PK o typie IOT-TOP co oznacza, że jest to tak naprawdę tabela zorganizowana w index. Właśnie w tym indeksie przechowywane są dodane dane.

Plan zapytania dla tabeli zorganizowanej według indeksu

Skoro wiemy, że dane są przechowywane w indeksie zobaczmy, czy plan zapytania SELECT również wskaże taką sekwencję wykonania.

Zobaczmy plan dla przykładowego zapytanie:

SELECT
    *
FROM OSOBA;

oraz plan zapytania:

Na planie zapytania widzimy wyszukiwanie metodą Index Fast Full Scan co jest zrozumiałe skoro wyszukujemy danych w indexie.

Można powiedzieć, że tabela “OSOBA” jest jedynie znacznikiem czy też synonimem indexu OSOBA_ID_PK na którym to wykonywane są operacje w bazie danych.

Dodatkowe indeksy na IOT

Może okazać się, że na tabeli zorganizowanej według indeksu wykonujemy zapytania nie tylko po kluczu głównym ale i po innych kolumnach. W takiej sytuacji możemy IOT utworzyć kolejny index.

Utwórzmy więc taki index wykonując poniższe polecenie:

CREATE INDEX OSOBA_IMIE_IDX ON OSOBA(IMIE);

Zobaczmy teraz co zobaczymy w widoku USER_INDEXES

SELECT
    *
FROM USER_INDEXES
WHERE TABLE_NAME = 'OSOBA';

oraz wynik

Jak widzimy w powyższym wyniku w bazie danych na tabeli OSOBA utworzone zostały dwa indeksy. OSOBA_ID_PK która jest naszą tabelą zorganizowaną według indeksu orz index OSOBA_IMIE_IDX który jest indeksem typu B-tree na kolumnie IMIE.

Zobaczmy w takim razie jakiś prosty plan zapytania dla wyszukiwania po kolumnie IMIE jak poniżej:

SELECT
    *
FROM OSOBA
WHERE IMIE = 'foo';

oraz wynik zapytania:

Powyższy plan pokazuje, że w baza danych w pierwszej kolejności wyszukuje rekordu o wartości IMIE=’foo’ w indexie OSOBA_IMIE_IDX a następnie pobiera resztę kolumn w indexie OSOBA_ID_PK.

Podsumowanie

• Tabela zorganizowana według indeksu (ang. Index Organized Tabel) w skrócie IOT
• Tabela zorganizowana według indeksu jest strukturą w której dane przechowywane są w strukturze podobnej do indexu B-tree
• Tabela IOT wymaga utworzenia klucza głównego
• Na tabeli IOT można tworzyć inne indeksy
• Jednoczenie z tworzeniem tabeli tworzny jest indeks na kluczu głównym w którym to zapisane są wszystkie dane z kolumn
• Tabela IOT nie może posiadać kolumn typu LONG
• Full Scan i Fast Full Scan na IOT zwracają dane w posortowanej kolejności