Teradata: Indeks główny – unikalny, nieunikalny a może jego brak?

Teradata: Indeks główny – unikalny, nieunikalny a może jego brak?

W niniejszym poście dowiesz się czym jest indeks główny tabeli (ang. PI – primary index) , dlaczego warto go określić oraz poznasz jego typy. Przeczytasz także o bardzo ważnej właściwości Teradaty – otóż jeśli nie określisz indeksu głównego, to nie oznacza, że go nie ma!

Indeks główny – komu to potrzebne?

Indeks główny w Teradacie pełni bardzo ważną funkcję – decyduje o tym, na którym AMPie będą przechowywane nasze dane. Jeśli zależy nam na wydajności zapytań, a wybierając Teradate z pewnością tak jest, to bardzo ważne jest, aby każdy z AMPów przechowywał zbliżoną ilość danych. Tak, równomierne rozłożenie danych decyduje o wydajności naszych operacji.

Każda tabela może posiadać tylko jeden indeks główny i musi on zostać określony podczas tworzenia tabeli. Nie jest możliwe określenie go podczas operacji ALTER.

Unikalny indeks główny (UPI)

Unikalny indeks główny (ang. UPI – Unique Primary Index) zapewnia unikalność danych oraz równomierne rozłożenie ich na poszczególnych AMPach. Co to oznacza? Spójrzmy na poniższy przykład. Mamy 6 pacjentów, jeden z nich jest w badaniu 2000, dwóch w badaniu 2010 oraz trzech z nich w badaniu 2050.

SUBJECT_ID STUDY_ID SUBJECT_NAME SUBJECT_STATUS
100 2000 Jan K 1
101 2010 Marzena N 1
102 2010 Anna P 2
103 2050 Mateusz L 1
104 2050 Jakub S 1
105 2050 Zygmunt A 1

Stwórzmy teraz tabelę SUBJECT z unikalnym indeksem głównym.

CREATE TABLE SUBJECT (
SUBJECT_ID INTEGER,
STUDY_ID INTEGER,
SUBJECT_NAME VARCHAR(100),
SUBJECT_STATUS BYTEINT )
UNIQUE PRIMARY INDEX UPI_SUBJECT (SUBJECT_ID);

Super! Stworzyliśmy już tabelę z unikalnym indeksem głównym. Załóżmy, że posiadamy 3 możliwe AMPy. Jak dane zostały na nich rozłożone? Każdy z nich dostał taką samą porcję rekordów.

Aby dokładniej wyjaśnić dlaczego tak się stało, musimy wprowadzić okrojone zagadnienie formuły mieszającej  (ang. hash formula), której Teradata używa do rozlokowania rekordów na poszczególnych AMPach. Każda wartość indeksu jest różna, ma więc także różny wynik formuły mieszającej. W związku z brakiem powiązań między wartościami hash formuły, rekordy są lokowane losowo, ale równomiernie na AMPach.

Nieunikalny indeks główny (NUPI)

Nieunikalny indeks główny (ang. NUPI – Non-Unique Primary Index) nie zapewnia unikalności danych oraz równomiernego ich rozłożenia na AMPach. W celu zobrazowania NUPI wykorzystajmy dane o pacjentach przedstawione w UPI. Stwórzmy tabele z nieunikalnym indeksem głównym:

CREATE TABLE SUBJECT (
SUBJECT_ID INTEGER,
STUDY_ID INTEGER,
SUBJECT_NAME VARCHAR(100),
SUBJECT_STATUS BYTEINT )
PRIMARY INDEX (STUDY_ID);

Ok, tabela z NUPI utworzona. Zauważymy tylko, że teraz indeks jest założony na kolumnie STUDY_ID, która nie jest unikalna. Jak teraz zostaną rozłożone rekordy na naszych 3 AMPach?

Wynik formuły mieszającej dla STUDY_ID równego 2050 jest identyczny w trzech przypadkach,dlatego rekordy te będą trzymane na jednym AMPie. Podobnie w przypadku rekordów z identyfikatorem 2010.

Jak więc widzimy, ilość danych w poszczególnych AMPach się różni – mówimy więc o rozkładzie nierównomiernym. Wyobraźmy sobie teraz sytuację, że jeden z naszych AMPów przechowuje 100 milionów rekordów, podczas gdy pozostałe przechowują ich tylko 10 tysięcy. Mamy do wykonania skomplikowane kalkulacje na naszych rekordach. AMPy, które zawierają 10 tysięcy rekordów będą w stanie obliczyć kalkulacje bardzo szybko (załóżmy 5 sekund), podczas gdy AMP zawierający 100 mln rekordów potrzebuje więcej czasu dla tych samych obliczeń (np. 1 minuta). Jaki będzie czas otrzymania wyniku naszego zapytania? Tak, będzie to 1 minuta – wynik otrzymamy kiedy wszystkie AMPy zakończą swoje kalkulacje. Dlatego równomierne rozłożenie danych na wszystkich AMPach jest tak ważne.

Wielokolumnowy indeks główny (Multi-Column PI)

Wielokolumnowy indeks główny (ang. Multi-Column Primary Index) zakładamy na wielu kolumnach (maksymalnie możemy użyć 64). 

Tabela z indeksem wielokolumnowym będzie wyglądać następująco:

CREATE TABLE SUBJECT (
SUBJECT_ID INTEGER,
STUDY_ID INTEGER,
SUBJECT_NAME VARCHAR(100),
SUBJECT_STATUS BYTEINT )
PRIMARY INDEX (SUBJECT_ID, STUDY_ID);

Mimo, że używamy tutaj indeksu nieunikalnego, włączeniu wielu kolumn do indeksu  zapewnia nam tutaj unikalność. Formuła mieszająca wykorzystuje obie kolumny, zapewniając równomierne rozłożenie danych w poszczególnych AMPach.

A co, jeśli nie chce mieć indeksu głównego?

Tworzenie tabel bez indeksu głównego jest często stosowane w zasilaniu tabel stage’owych (ang. staging tables). Pozwala ono na losowe, lecz równomierne rozłożenie rekordów na wszystkich AMPach. Jeśli nie chcesz, aby Twoja tabela miała indeks główny, musisz to uwzględnić w definicji tabeli!

CREATE TABLE SUBJECT (
SUBJECT_ID INTEGER,
STUDY_ID INTEGER,
SUBJECT_NAME VARCHAR(100),
SUBJECT_STATUS BYTEINT )
NO PRIMARY INDEX ;

Brak powyższego polecenia NO PRIMARY INDEX, spowoduje wywołanie właściwości Teradaty, która polega na przypisaniu pierwszej kolumny jako nieunikalnego indeksu głównego (NUPI). Sprawdźmy! Utwórzymy sobie tabele bez definiowania indeksu:

CREATE TABLE SUBJECT (
SUBJECT_ID INTEGER,
STUDY_ID INTEGER,
SUBJECT_NAME VARCHAR(100),
SUBJECT_STATUS BYTEINT );

A teraz sprawdzimy, czy nasza tabela dostała indeks główny:

SELECT DATABASENAME, TABLENAME, COLUMNNAME, INDEXTYPE, INDEXNUMBER, INDEXNAME
FROM DBC.INDICES
WHERE DATABASENAME='<your database name>' AND TABLENAME='SUBJECT';

Zgadza się, indeks główny został założony na pierwszą kolumnę (SUBJECT_ID).

Jeśli spodobał Ci się ten post to zostaw proszę komentarz poniżej lub udostępnij ten post na swoim Facebook’u, Twitter’ze, LinkedIn lub innej stronie z mediami społecznościowymi.
Dzięki!

Leave a Reply

avatar
  Subscribe  
Powiadom o
Close Menu