Indeksite loomine
Indeksi loomine
Nagu loengus rääkisime, loome indekseid eelkõige neile atribuutidele, mida sageli kasutame päringu WHERE osas. Indeksi loomine:
CREATE INDEX indeksi_nimi ON tabeli_nimi [USING method]
(veerunimi [ASC | DESC] [NULLS {FIRST | LAST }],
...
);
Veerunimesid võib olla üks (ühe välja indeks) või mitu (mitme välja indeks). Vaikimisi on veeru järjekord ASC, kui tahta muuta, peab kindlasti lisama DESC. Samamoodi on vaikimisi NULLS LAST (ehk siis tühjad väljad lõpus) kui järjekord on ASC ja NULLS FIRST kui järjekord on DESC.
Loome indeksi isikute tabelile võttes indeksiväljaks isikukoodi:
CREATE INDEX ix_isikukood ON Isikud (isikukood);
Siin kuna me ei määratlenud, kas on järjestus kasvav või kahanev, siis vaikimisi võeti kasvav (ASC) järjestus. Kui isikukoodi väli võib olla sisestamata, siis sellisel juhul tühjad väljad on lõpus. Kui tahame, et isikukoodi indeks oleks kahaneva järjestuse peale, aga ikka nii, et tühjad väljad oleks kõige lõpus, peame kirjutama:
CREATE INDEX ix_isikukood_teistpidi ON Isikud (isikukood DESC NULLS LAST);
Meeldetuletus loengust, indekseid luuakse veergudele, millel on palju unikaalseid väärtusi (meil isikukood on selline) ja ei looda veergudele, millel on palju NULL väärtusi.
Taas teeme kokkuleppe nimede osas. Oma aines indekseid luues kirjutame indeksi nime ette ix_. Ja veelkord ka meeldetuletus, et see pole üldlevinud, igas töörühmas on oma reeglid ja kokkulepped.
Olemasolevad indeksid ja indeksi detailide vaatamine DBeaver rakenduses
Olemasolevaid indekseid näeb DBeaver rakenduses tabeli juurest, millele me indeksi lisasime. Vajadusel uuendage (Refresh).
Uuendades DBeaver vaates indeksite nimekirja näeme, et loodud indeks on ilmunud teiste indeksite hulka. Primaarvõtmed ja unikaalsuse kitsendused lähevad automaatselt indeksiteks.
Olemasoleva indeksi loomise SQL käsku näeb, kui indeksi peal parempoolse hiireklahviga valida ”Generate SQL” ja uues avanenud menüüs “DDL”, mille tulemusena avaneb uues aknas valitud indeksi loomise SQL lause.
Nagu loengus rääkisime, siis kui WHERE osas kasutatakse sageli mitme tunnuse kombinatsiooni, on otstarbekas luua indeks, mis kasutaks mitut atribuuti ehk siis mitme välja indeks. Näiteks meie male andmebaasis on sageli vaja isikuid koos ees- ja perenimega. Loome selleks kahe atribuudiga indeksi:
CREATE INDEX ix_nimi ON Isikud (perenimi, eesnimi);
PostgreSQL'is saab täpsustada, kas indeks on unikaalsele väljale loodud (indeksi loomise lauses täpsustus UNIQUE). Kui valida unikaalne indeks UNIQUE, siis peab olema tabeli definitsioonis vastav atribuut unikaalsena defineeritud. Kui aga see unikaalne atribuut sisaldab ka tühje väätusi (NULL väätusi), siis peaks lisama veel indeksi loomise lause lõppu klausli: NULLS [NOT] DISTINCT vastavalt sellele, kas tühje väärtusi eristada või mitte.
CREATE INDEX indeksi_nimi ON tabeli_nimi USING indeksi_tüüp (atribuudid);
Indeksi tüüp määrab ära algoritmi, mida indeks kasutab. PostgreSQL versioon 14 kasutab vaikimisi (kui indeksi tüüp ei ole valitud) tüübi B-Tree. Rohkem informatsiooni erinevate indeksi tüüpide kohta saab lugeda PostgreSQL dokumentatsioonist aadressil https://www.postgresql.org/docs/14/indexes-types.html.
Indekseid saab luua ka materialiseeritud vaadetele, kuid mitte tavalisetele vaadetele. Loome indeksi neljandas kodutöös loodud materialiseeritud vaatele mv_partiide_arv_valgetega, kus indeksi väljaks on partiisid_valgetega.
CREATE INDEX ix_partiisid ON mv_partiide_arv_valgetega (partiisid_valgetega);
NB! Indekseid ei saa luua kui keegi teine kasutab tabelit!
Indeksit kustutatakse
DROP INDEX <indeksi_nimi>;
Kontrollimaks, kas said indeksite loomisest aru ava soovi korral test.
AVA TEST
Link PostgreSQL dokumentatsioonile
| Protseduuride loomine | Trigerite loomine |