Pokročilé databázové systémy umožňují zefektivnit dotazy pomocí indexů a zjednodušit dotazování nad daty pomocí pohledů, použití uložených procedur. Zárověň pomocí triggerů (spouštěčů) umožňují ovlivňovat výsledky zapisovacíh operací (INSERT, UPDATE,DELETE), případně jejich prostřednictvím vyjádřit složitější integritní omezení (včetně minimálních a maximálních parcialit)

1. pokročilé databázové techniky a SQL osnova7
   1. pohledy
   2. indexy
   3. uživatelsky definované typy
   4. uživatelsky definované funkce
   5. uložené procedury
   6. kurzory
   7. triggery

Pokud se při různých dotazech opakují spojování tabulek, podmínky či agregace; nebo je-li účelné zviditělnit některou (pod)sestavu, je možné tuto opakující se část nahradit pohledem. Pohled typicky koncovému uživateli umožňuje náhled na data bez použití denormalizace, zatímco data jsou bezpečně uložena v normovaném tvaru.

Pohled může být

• nematerilizovaný - v takovém případě se chová jako v prostoru deklarací tabulek vnořený SELECT, t.j. zejména je evaluován v okamžiku volání dotazu
• materilizovaný - je to tabulka prostředky databázového systému (zejména triggery) svázaná se “zdrojovými” tabulkami

Nematerializované pohledy se vytvářejí pomocí CREATE VIEW . Pro vytváření materializovaných pohledů lze standardně použít konstrukce CREATE TABLE AS a následně ji doplnit o volání triggerů nad zdrojovými tabulkami zajištující aktuálnost dat pohledu; některé databáze umožňují tento proces zautomatizovat pomocí konstrukce CREATE MATERIALIZED VIEW .

Ačkoliv do pohledu nelze zapisovat, lze tuto funkcionalitu doplnit pomocí tzv. pravidel, viz CREATE RULE. Některé databázové systémy tyto pravidla zavádějí defaultně při založení pohledu

Vytvořte sestavu pro vypsání položek na faktuře:

CREATE VIEW fakturalist AS SELECT
  id_faktura,
  zbozi.nazev, ks,
  fakturazbozi.cena AS cena_ks, fakturazbozi.sleva AS sleva, 
  fakturazbozi.cena * fakturazbozi.ks * (CASE WHEN fakturazbozi.sleva IS NULL THEN 1 ELSE 1-fakturazbozi.sleva END) as cenacelkem

FROM zbozi
  JOIN zbozifaktura USING (id_zbozi)

Indexací se rozumí rodina optimalizačních technik sloužících k urychlení vykonávání SQL dotazů. Nejprve si připomeňme, jak PostgreSQL vykonává SQL dotazy. Při vyhodnocení SELECT dotazu plánovač dotazů v PostgreSQL serveru sestaví tzv. query plan, tedy plán, jak se dotaz vykoná. Tento plán si můžeme prohlédnout pomocí klíčového slova EXPLAIN před příslušným dotazem, např. (viz. příklad databáze dále v tomto cvičení)

EXPLAIN ANALYZE SELECT count(*) FROM mytable;

dává výsledek

                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1693.00..1693.01 rows=1 width=0) (actual time=251.654..251.655 rows=1 loops=1)
   ->  Seq Scan on mytable  (cost=0.00..1443.00 rows=100000 width=0) (actual time=0.025..127.282 rows=100000 loops=1)
 Total runtime: 251.721 ms

Vidíme zde, že se provádí agregace (Aggregate) na základě výsledků sekvenčního scanu tabulky mytable.

Plánovač dotazů se snaží optimalizovat vyhodnocení dotazu. Využívá pro to různých technik, např. odhadu počtu řádků v dané tabulce. Tyto odhady je třeba čas od času aktualizovat příkazem ANALYZE. Je však možné v konfiguraci PostgreSQL zapnout AUTOVACUUM démon, který se o to v pravidelných intervalech postará.

Malá vsuvka o tom, proč nejsou statistiky aktualizovány při každé změně dat v DB. Důvodem je to, že pri DELETE/UPDATE se musi zachovat historicke (puvodni) verze aktualizovanych/mazanych radku pro transakce, ktere si drive zamkli tyto radky pro cteni. Podobne 'mrtve radky' mohou vzniknout pri rollbacku transakce. K temto mrtvym radkum se neni mozne jiz dostat, je proto treba je smazat, aby nezabiraly misto (to se provede příkazem VACUUM). Tyto řádky však nejsou z výkonnostních důvodů mazány automaticky - zbytečně by to zabíralo čas.

V postgresql.conf je nastaveno “autovacuum off”, takže statistiky nejsou aktualizovány automaticky. Vytvoříme testovací tabulku. Vsimnete si, ze se automaticky vytvori index na primarni klic.

CREATE TABLE mytable( id serial PRIMARY KEY, value int );

Vygenerujeme testovaci data

INSERT INTO mytable(value) SELECT trunc(1000*random()) FROM generate_series(1,100000);

V tomto okamziku nejsou statistiky spocitany, nebot autovacuum=off. Overime takto:

SELECT relname, reltuples FROM pg_class WHERE relname='mytable';

Vyčistíme staré řádky a aktualizujeme statistiky:

VACUUM ANALYZE;

A znovu podivame se na aktualizovane statistiky

SELECT relname, reltuples FROM pg_class WHERE relname='mytable';

Pouzijeme-li primarni klic jako filtr, vidime, ze se pouzije index implicitne vygenerovany pri tvorbe primarniho klice (index scan) misto mene sekvencniho scanu. Index scan se vyplati, je-li filtr hodne selektivni (napr. zde 100/100000).

EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE id=100;

Úkol 1: Srovnejte s dotazem, ktery filtruje podle sloupce, ktery nema index. Jaky typ scanu se pouziva a proc ? Srovnejte runtime casy v obou prikladech.<file>EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE value=100;</file>

Zkusime vytvorit index pomocí konstrukce CREATE INDEX:

CREATE INDEX i on mytable(value);

Úkol 2: Ověřte, jak se změní vyhodnocení předchozího SELECTu.

Lepší výsledek indexu je dán nejen indexem, ale i částečně cachováním výsledků předchozího dotazu (bez indexace) v PostgreSQL. Můžete si prohlédnout statistiky počty jednotlivých typů scanů/přístupů na disk v pg_stat_user_tables, resp. pg_stat_user_tables. Před každým takto testovaným dotazem je však třeba tyto statistiky vynulovat (může pouze administrátor databáze) pomocí

SELECT pg_stat_reset();

Úkol 3: Indexy mají výhodu pouze, má-li dotaz, který index využívá, vysokou selektivitu. V opačném případě může plánovač zvolit sekvenční scan navzdory existujícímu indexu. Abychom to ověřili, zkuste smazat všechna data z tabulky a vygenerovat data ještě jednou s většími množinami stejných hodnot (např. 10*random()). Pote naleznete hranici X filtru WHERE value < X v predchozim dotazu, pri kterem k prepnuti na sekvencni scan dojde. Porovnejte časy vyhodnocovaní dotazu v hranicnich pripadech.

Unikátní omezení patří k nejčastějším. Lze je vytvořit v zásadě třemi způsoby: (i) automaticky vytvořením primárního klíče, (ii) klíčovým slovem UNIQUE za definicí atributu v CREATE TABLE, (iii) vytvoření indexu CREATE UNIQUE INDEX, který bude unikátnost kontrolovat.

Úkol 4: Napadá vás, jaký je rozdíl mezi označením atributu UNIQUE a vytvořením unikátního indexu CREATE UNIQUE INDEX na tento atribut (zkuste si oba vytvořit) ? Který způsob je z hlediska údržby databáze výhodnější, a proč ?

Lze vytvářet i vícesloupcové indexy. Pokud vytvoříme např. index pro (id,value) v příkladě nahoře, výsledný index bude patrně menší než dva samostatné indexy na id a na value, ovšem jeho použitelnost bude pouze na dotazy, které buď používají filtr přes 'id', nebo přes id a value, ovšem nikoliv přes 'value' samostatně.

Úkol 5: Vyzkoušejte si, jak se liší doba vyhodnocení různých typů dotazů pro vícesloupcový index a pro více jednosloupcových indexů.

O indexech pojednávají například i články na rootu. Například Využití databázových indexů nebo Postgre Guide - Indexes. Tutoriál obdobný tomu, který je uveden výše lze najít ještě na tutorialspoin.com/postgresql_indexes.htm.

Uložené procedury, někdy označované jako databázové funkce, umožňují definovat složitější uživatelské výpočty. Různé databázové systémy podporují různé procedurální jazyky.

Vytvořte funkci pro exponenciální zapomínání.

CREATE OR REPLACE FUNCTION oblivion(timestamp with time zone) RETURNS float AS $_$
  SELECT exp((extract(epoch from $1) - extract (epoch from now()))/(3600*24*2)); $_$ LANGUAGE 'sql';

Všimněte si, že je bezpodíněčně nutné, aby SELECT vrátil pouze jednu hodnotu (identicky se vnořeným SELECTem na pozici hodnot). U uložených procedur je navíc kontrolována shoda mezi deklarovaným a vráceným typem.

Vytvořte funkci nahrazující funkcionalitu jednoatributového textového primárního klíče (t.j. funkci, která převede textový primární klíč na int)

CREATE OR REPLACE FUNCTION indexzbozi(varchar) RETURNS int AS $_$
DECLARE
ret	int;
BEGIN
ret = (SELECT id_zbozi FROM zbozi WHERE nazev=$1);
IF ret IS NULL THEN 
    ret=nextval('seq_zbozi');
    INSERT INTO zbozi(id_zbozi,nazev) VALUES (ret,$1);
    RAISE NOTICE 'indexzbozi (%) - nove id=%',$1, ret;
ELSE
    RAISE NOTICE 'indexzbozi (%) = %',$1,ret ;
END IF;
RETURN ret;
END; $_$ LANGUAGE 'plpgsql' ;

Použití:

SELECT indexzbozi('ponozky');

Jazyk plpgsql umožňuje zejména větvení, cykly i obsluhu vyjímek.

Uložené procedury nemusejí vracet pouze jednu hodnotu, ale i celou tabulku.

Vytvořte tabulkovou uloženou proceduru na výtisk faktury.

CREATE OR REPLACE FUNCTION polozkyfaktury(int) RETURNS set of fakturalist AS $_$
  SELECT * FROM fakturalist WHERE id_faktura = $1; $_$ LANGUAGE 'sql';

Použití:

SELECT * FROM polozkyfaktury(10) AS seznam;

Pokud vrácený výsledek neodpovídá žádnému existujícímu typu, pohledu či tabulce, je možné použít generický typ RECORD. Při dotazování je však nezbytně v SELECTu mnutné provést výčet atributů včetně jejich typů.

Se zapisovacími operacemi lze svázat volání uložených procedur pomocí triggerů.

Trigger může být volán

• před nebo po provedení operace
• pro každý záznam nebo pro každý příkaz (příkaz může ovlivnit více záznamů)

Pokud obslužná procedura triggeru před voláním operace vrátí NULL, operace je zrušena (t.j. neprovede se). Podobně obslužná procedura může vyvolat vyjímku - opět dochází k přerušení operace.

Příklad:

CREATE OR REPLACE FUNCTION trg_odectizeskladuzbozi() RETURNS TRIGGER AS $_$
BEGIN
  UPDATE sklad SET ks = ks - NEW.ks WHERE id_zbozi = NEW.id_zbozi;
  RETURN NEW;
END;  $_$ LANGUAGE 'plpgsql' 

CREATE TRIGGER trg_odectizeskladuzbozi BEFORE INSERT TO fakturazbozi FOR EACH ROW EXECUTE PROCEDURE trg_odectizeskladuzbozi;

Dalsi priklad triggeru realizujici slozitejsi integritni omezeni - viz http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html