Materializované pohledy

Materializované pohledy jsou databázové objekty, podobně jako „nematerializované“ pohledy. Jenom se výsledek SQL dotazu někam uloží. Dotaz na materializovaný pohled je proto mnohem rychlejší (data už jsou připravena), ale zase nemusí být aktuální (data se připravují na požádání). A to je zhruba všechno důležité, co se dá o materializovaných pohledech říci.

Příprava tabulek

No dobře, můžu o materializovaných pohledech ještě něco napsat. A ukázat pár příkladů. V této kapitole budu ukazovat příklady na tabulkách host,page a referrer, jejichž definice máte pro všechny databáze níže. Zároveň jsem vám připravil skripty pro nahrání dat do tabulek.

DROP TABLE IF EXISTS referrer;
DROP TABLE IF EXISTS page;
DROP TABLE IF EXISTS host;
DROP TYPE error_enum;
CREATE TYPE error_enum AS ENUM ('no','yes','connection');


CREATE TABLE host (
    id SERIAL PRIMARY KEY NOT NULL,
    host VARCHAR(255) NOT NULL UNIQUE,
    domain VARCHAR(50),
    finded timestamp without time zone,
    checked_pages_count integer DEFAULT 0 NOT NULL
);

CREATE TABLE page (
    id SERIAL PRIMARY KEY NOT NULL,
    page VARCHAR(512) NOT NULL,
    port integer DEFAULT 80 NOT NULL,
    host_id integer  REFERENCES host(id),
    finded timestamp without time zone,
    checked boolean DEFAULT false NOT NULL,
    checked_at timestamp without time zone,
    body text,
    finded_in integer,
    schema VARCHAR(10) DEFAULT 'http' NOT NULL,
    contenttype VARCHAR(50) DEFAULT '' NOT NULL,
    charset VARCHAR(50) DEFAULT '' NOT NULL,
    base VARCHAR(255) DEFAULT '',
    error error_enum DEFAULT 'no' NOT NULL,
    CONSTRAINT page_finded_in_fkey FOREIGN KEY (finded_in) REFERENCES page(id),
    CONSTRAINT page2_check1 CHECK ((checked_at IS NULL) OR (checked = true))
);

CREATE TABLE referrer (
    id SERIAL PRIMARY KEY NOT NULL,
    referrer_id integer NOT NULL REFERENCES page(id),
    page_id integer NOT NULL REFERENCES page(id)
);

CREATE UNIQUE INDEX referrer_uix ON referrer (referrer_id, page_id);
mview-psql.sql.zip
DROP TABLE IF EXISTS referrer;
DROP TABLE IF EXISTS page;
DROP TABLE IF EXISTS host;

CREATE TABLE host (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    host VARCHAR(255) NOT NULL UNIQUE,
    domain VARCHAR(50),
    finded DATETIME,
    checked_pages_count integer DEFAULT 0 NOT NULL
);

CREATE TABLE page (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    page VARCHAR(512) NOT NULL,
    port integer DEFAULT 80 NOT NULL,
    host_id integer,
    finded DATETIME,
    checked boolean DEFAULT false NOT NULL,
    checked_at DATETIME,
    body LONGTEXT,
    finded_in integer,
    `schema` VARCHAR(10) DEFAULT 'http' NOT NULL,
    contenttype VARCHAR(50) DEFAULT '' NOT NULL,
    charset VARCHAR(50) DEFAULT '' NOT NULL,
    base VARCHAR(255) DEFAULT '',
    error ENUM ('no','yes','connection') DEFAULT 'no' NOT NULL,
    FOREIGN KEY (host_id) REFERENCES host(id),
    FOREIGN KEY (finded_in) REFERENCES page(id)
);

CREATE TABLE referrer (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    referrer_id integer NOT NULL,
    page_id integer NOT NULL,
    FOREIGN KEY (referrer_id) REFERENCES page(id),
    FOREIGN KEY (page_id) REFERENCES page(id)
);

CREATE UNIQUE INDEX referrer_uix ON referrer (referrer_id, page_id);
mview-mysql.sql.zip
DROP TABLE IF EXISTS referrer;
DROP TABLE IF EXISTS page;
DROP TABLE IF EXISTS host;

CREATE TABLE host (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    host VARCHAR(255) NOT NULL UNIQUE,
    domain VARCHAR(50),
    finded DATETIME,
    checked_pages_count integer DEFAULT 0 NOT NULL
);

CREATE TABLE page (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    page VARCHAR(512) NOT NULL,
    port integer DEFAULT 80 NOT NULL,
    host_id integer,
    finded DATETIME,
    checked boolean DEFAULT false NOT NULL,
    checked_at DATETIME,
    body LONGTEXT,
    finded_in integer,
    `schema` VARCHAR(10) DEFAULT 'http' NOT NULL,
    contenttype VARCHAR(50) DEFAULT '' NOT NULL,
    charset VARCHAR(50) DEFAULT '' NOT NULL,
    base VARCHAR(255) DEFAULT '',
    error VARCHAR(10) CHECK (error in ('no','yes','connection')) DEFAULT 'no' NOT NULL,
    FOREIGN KEY (host_id) REFERENCES host(id),
    FOREIGN KEY (finded_in) REFERENCES page(id),
    CONSTRAINT page2_check1 CHECK ((checked_at IS NULL) OR (checked = 1))
);

CREATE TABLE referrer (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    referrer_id integer NOT NULL,
    page_id integer NOT NULL,
    FOREIGN KEY (referrer_id) REFERENCES page(id),
    FOREIGN KEY (page_id) REFERENCES page(id)
);

CREATE UNIQUE INDEX referrer_uix ON referrer (referrer_id, page_id);
mview-sqlite3.sql.zip
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE referrer';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
    END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE page';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
    END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE host';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
    END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE host_id_seq';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE page_id_seq';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE referrer_id_seq';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/

CREATE TABLE host (
    id integer PRIMARY KEY NOT NULL,
    host VARCHAR2(255) NOT NULL UNIQUE,
    domain VARCHAR2(50),
    finded timestamp,
    checked_pages_count integer DEFAULT 0 NOT NULL
);

CREATE SEQUENCE host_id_seq;


CREATE TABLE page (
    id INTEGER PRIMARY KEY NOT NULL,
    page VARCHAR2(512) NOT NULL,
    port INTEGER DEFAULT 80 NOT NULL,
    host_id INTEGER  REFERENCES host(id),
    finded TIMESTAMP,
    checked NUMBER(1,0) DEFAULT 0 NOT NULL,
    checked_at TIMESTAMP,
    body CLOB,
    finded_in INTEGER,
    schema VARCHAR2(10) DEFAULT 'http' NOT NULL,
    contenttype VARCHAR2(50) DEFAULT '',
    charset VARCHAR2(50) DEFAULT '',
    base VARCHAR2(255) DEFAULT '',
    error VARCHAR2(10) DEFAULT 'no' NOT NULL CHECK( error in ('no','yes','connection')),
    CONSTRAINT page_finded_in_fkey FOREIGN KEY (finded_in) REFERENCES page(id),
    CONSTRAINT page2_check1 CHECK ((checked_at IS NULL) OR (checked = 1))
);

CREATE SEQUENCE page_id_seq;

CREATE TABLE referrer (
    id INTEGER PRIMARY KEY NOT NULL,
    referrer_id INTEGER NOT NULL REFERENCES page(id),
    page_id integer NOT NULL REFERENCES page(id)
);

CREATE UNIQUE INDEX referrer_uix ON referrer (referrer_id, page_id);
CREATE SEQUENCE referrer_id_seq;
mviewOracle.sql.zip
Skript nejde nahrát v Apexu, protože je moc velký. Můžete jej nahrát v SQL Developeru nebo v příkazové řádce.

Popis tabulek

Co tabulky obsahují není pro tuto kapitolu nijak důležité. Ani není důležité jak se používá datový typ enum, nebo drobné rozdíly mezi definicemi tabulek v různých databázích. Jsou to tabulky, které jsem převzal z jednoho projektu, kde jsem se snažil stáhnout obsah celého internetu :-) a náhodou jsem se rozhodl, že je použiji pro tuto kapitolu.

V tabulce host jsou nejdůležitejší sloupce host (například www.sallyx.org) a domain (například cz, org, info atp.).

V tabulce page jsou to soupce page (například /, /sally/, /sally/psql/materializovane-pohledy.php atp.) a host_id (odkaz na doménu).

Do tabulky referrer se pak zaznamenává, v jaké stránce (referrer_id) byla nalezena jaká stránka (page_id). A samozřejmě vyčtete i obráceně, jaké stránky byly nalezeny ve nějaké stránce, která vás zajímá.

Postgres

Materializovaný pohled se vytváří jako normální pohled, jen uvedete navíc slovíčko MATERIALIZED. A můžete ještě připsat WITH NO DATA, pokud nechcete, aby se pohled hned materializoval (aby se provedl SQL dotaz pohledu a výsledek se kamsi uložil).

Vytvořím pohled, který mi najde stránky, na které je nejčastěji odkazováno.

rimmer1=> CREATE MATERIALIZED VIEW most_referred_pages
AS
SELECT referrer.page_id, count(*) AS referred
FROM referrer
GROUP BY referrer.page_id
WITH NO DATA;

SELECT 0

Protože jsem použil WITH NO DATA, bude pohled prázdný, resp. neinicializovaný.

rimmer1=> SELECT * from most_referred_pages;
ERROR:  materialized view "most_referred_pages" has not been populated
DOPORUČENÍ:  Use the REFRESH MATERIALIZED VIEW command.

K naplnění pohledu se používá příkaz REFRESH MATERIALIZED VIEW nazev_pohledu; Po jeho naplnění si mohu zobrazit top 10 nejoblíbenějších stránek.

rimmer1=> REFRESH MATERIALIZED VIEW  most_referred_pages;
REFRESH MATERIALIZED VIEW

rimmer1=> SELECT * FROM most_referred_pages ORDER BY referred DESC limit 10;
 page_id | referred
---------+----------
    9776 |       12
    9775 |       11
    9840 |       10
    9990 |       10
    9839 |       10
    9774 |        9
    9841 |        8
    9838 |        8
   10032 |        8
    9935 |        7

Nejčastěji odkazovaná stránka je stránka s ID 9776 :-).

Materializované pohledy se automaticky nerefreshují. To znamená, že pokud změním cokoliv v tabulkách, ze kterých se materializovaný pohled vytváří, nic to na jeho obashu nezmění, dokud nezavoláte REFRESH MATERIALIZED VIEW. Tento příkaz obsah materializovaného pohledu smaže, znovu zavolá SQL dotaz a pohled materializuje. Vše je součástí jedné transakce a tak se nemůže stát, že by si někdo v mezičase stihnul přečíst prázdný (zrovna smazaný) pohled před jeho opětovným naplněním.

Materializované pohledy se hodí především tam, kde nepotřebujete aktuální data, ale chcete je rychle. (Například pro statistiky za minulý týden atp.). Případně se tak dají jednoduše replikovat data mezi různými servery (to už je ale nad rámec tohoto kurzu).

Podívejte se, jak si naplánuje Postgres práci pro následující SQL dotaz, který se snaží zjistit podrobnosi o top 10 odkazovaných stránkách:

rimmer1=> EXPLAIN
SELECT referred, page.schema, host.host, page.page
FROM most_referred_pages
JOIN page ON page.id = page_id
JOIN host on host.id = host_id
ORDER BY referred DESC
LIMIT 10;
                                           QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
 Limit  (cost=283.93..283.95 rows=10 width=52)
   ->  Sort  (cost=283.93..289.75 rows=2328 width=52)
         Sort Key: most_referred_pages.referred
         ->  Hash Join  (cost=131.41..233.62 rows=2328 width=52)
               Hash Cond: (page.host_id = host.id)
               ->  Hash Join  (cost=111.79..181.99 rows=2328 width=41)
                     Hash Cond: (most_referred_pages.page_id = page.id)
                     ->  Seq Scan on most_referred_pages  (cost=0.00..35.28 rows=2328 width=12)
                     ->  Hash  (cost=84.13..84.13 rows=2213 width=37)
                           ->  Seq Scan on page  (cost=0.00..84.13 rows=2213 width=37)
               ->  Hash  (cost=13.72..13.72 rows=472 width=19)
 

Žádná sláva. A to je v tabulkách relativně málo záznamů. Je to samý sekvenční čtení. Asi by to chtělo nějaký index. To pro materializovaný pohled není žádný problém :-).

rimmer1=> CREATE INDEX referred_ix ON most_referred_pages(referred);
DEBUG:  building index "referred_ix" on table "most_referred_pages"
CREATE INDEX
rimmer1=> explain SELECT referred, page.schema, host.host, page.page FROM most_referred_pages JOIN page ON page.id = page_id JOIN host on host.id = host_id ORDER BY referred DESC limit 10;
                                                       QUERY PLAN                                                      
------------------------------------------------------------------------------------------------
 Limit  (cost=0.83..9.00 rows=10 width=52)
   ->  Nested Loop  (cost=0.83..1808.94 rows=2213 width=52)
         ->  Nested Loop  (cost=0.56..1087.51 rows=2213 width=41)
               ->  Index Scan Backward using referred_ix on most_referred_pages  (cost=0.28..117.47 rows=2213 width=12)
               ->  Index Scan using page_pkey on page  (cost=0.28..0.43 rows=1 width=37)
                     Index Cond: (id = most_referred_pages.page_id)
         ->  Index Scan using host_pkey on host  (cost=0.27..0.32 rows=1 width=19)
               Index Cond: (id = page.host_id)
(8 řádek)

No vida, výsledná odhadovaná doba klesla z 283.95 jednotek na 9.00.

Materializované pohledy můžete používat stejně jako běžné tabulky. Jen je většinou nemůžete přímo updatovat (můžete si napsat INSTEAD OF trigger).

Materializované pohledy jsou novinkou v Postgresu 9.3. Autoři do budoucna slibují další vylepšení. Pokud chcete vědět, co všechno by to mohlo být, čtětě dále v části o Oracle, co on všechno dovede.

MySQL

MySQL materializované pohledy neumí. Tím bych mohl výklad ukončit, ale protože mě materializované pohledy baví, ukáži vám, jak se dají v MySQL simulovat. Stačí k tomu jednoduchý CREATE TABLE ... AS SELECT ... statement.

mysql> CREATE TABLE most_referred_pages AS
SELECT referrer.page_id, count(*) AS referred
FROM referrer GROUP BY referrer.page_id
LIMIT 0;

LIMIT 0 tu funguje jako WITH NO DATA.

Místo příkazu REFRESH MATERIALIZED VIEW můžete použít tyto příkazy:

LOCK TABLE most_referred_pages WRITE,referrer READ;
DELETE FROM most_referred_pages;
INSERT INTO most_referred_pages
        SELECT referrer.page_id, count(*) AS referred
        FROM referrer GROUP BY referrer.page_id;
UNLOCK TABLES;

Ještě vytvořím tabulce indexy, které si zaslouží:

CREATE INDEX referred_ix ON most_referred_pages(referred);
ALTER TABLE most_referred_pages ADD FOREIGN KEY (page_id) REFERENCES page(id);

A tímto bych mohl výklad ukončit. Ale proč nevyužít příležitosti a nezopakovat si trochu triggery …

Automatický update

Pomocí triggerů můžete aktualizovat tabulku, která slouží jako materializovaný pohled, okamžitě při změně tabulek, na kterých je materializovaný pohled závislý.

Následuje ukázka triggerů, které aktualizují tabulku most_referred_pages při změne tabulky referrer (INSERTem nebo DELETEm).

DROP TRIGGER IF EXISTS refresh_mrp_on_insert;
DROP TRIGGER IF EXISTS refresh_mrp_on_delete;
DELIMITER //

CREATE TRIGGER refresh_mrp_on_insert AFTER INSERT ON referrer
FOR EACH ROW
BEGIN
        DECLARE was_updated INTEGER;
        UPDATE most_referred_pages SET referred = referred + 1 WHERE page_id = NEW.page_id;
        SELECT ROW_COUNT() INTO was_updated;
        IF (was_updated = 0)
        THEN
                INSERT INTO most_referred_pages VALUES(NEW.page_id, 1);
        END IF;
END;

CREATE TRIGGER refresh_mrp_on_delete AFTER DELETE ON referrer
FOR EACH ROW
BEGIN
        UPDATE most_referred_pages SET referred = referred - 1 WHERE page_id = OLD.page_id;
END;
//
DELIMITER ;

Trigger pro UPDATE jsem vynechal, protože tabulku referrer nemá smysl updatovat, ale v reálné aplikaci byste měli alespoň updaty zakázat (viz přístupová práva), abyste měli zajištěnou konzistenci databáze. Trigger pro DELETE taky neřeší případ, že referrer klesne na 0. Mít záznam s 0 a nemít záznam není totéž. Při refreshi celé tabulky by záznam v tabulce nebyl. Jestli to vadí nebo ne, to už je na vás :-). Problém můžete vyřešit jednoduchým příkazem navíc: DELETE ... WHERE page_id = OLD.page_id AND referred = 0;.

Samozřejmě ne vždy je takovýto UPDATE „materializovaného pohledu“ možný. Stačilo by, abych vynechal při vytváření tabulky most_referred_pages sloupec page_id a už se nechytáte. A protože se materializované pohledy vytvářejí především pro složitější SQL dotazy, plné JOINů a analytických funkcí, bude to procento tabulek, které nepůjde takto updatovat, poměrně velké.

Musíte taky vzít v úvahu snížení rychlosti updatů do tabulky referrer. Každý INSERT znamená vyvolání triggeru a insert/update do další tabulky most_referred_pages.

Když jsem psal svůj program na stahování internetu, čekal jsem, že internetové připojení bude to, co mě bude brzdit. Ve skutečnosti jsou to právě inserty do databáze, které mě zpomalují nejvíce.

A tímto bych povídání o materializovaných pohledech v MySQL ukončil :) Jen dodám co je vám asi stejně jasné, totiž že stejným způsobem se dá řešit problém automatického updatu i v Postgresu (bez využití skutečných materializovaných pohledů, které, na rozdíl od tabulky, updatovat nejdou).

SQLite

SQLite materializovné pohledy nepodporuje, takže se u něj dá napsat to samé, co u MySQL. Jen je potřeba dát si pozor na ty drobné rozdíly mezi databázemi.

SQLite neumí přidat FOREIGN KEY k již existující tabulce, takže si nejdříve musím vytvořit tabulku a pak jí teprve mohu plnit.

DROP TABLE IF EXISTS most_referred_pages;
CREATE TABLE most_referred_pages(page_id INT REFERENCES page(id) UNIQUE, referred INT);
CREATE INDEX referred_ix ON most_referred_pages(referred);

Co se plnění týče, je to stejné jako u MySQL, jen SQLite nepodporuje LOCK TABLE:

DELETE FROM most_referred_pages;
INSERT INTO most_referred_pages
       SELECT referrer.page_id, count(*) AS referred
       FROM referrer GROUP BY referrer.page_id;

A v triggeru neumožňuje používat podmínky IF nebo WHEN, takže trigger AFTER INSERT vypadá trochu jinak. Využívá toho, že jsem udělal sloupec most_referred_pages.page_id unikátní (což jsem vlastně mohl udělat i v MySQL):

DROP TRIGGER IF EXISTS refresh_mrp_on_insert;
DROP TRIGGER IF EXISTS refresh_mrp_on_delete;

CREATE TRIGGER refresh_mrp_on_insert AFTER INSERT ON referrer
FOR EACH ROW
BEGIN
        UPDATE most_referred_pages SET referred = referred + 1 WHERE page_id = NEW.page_id;
        INSERT OR IGNORE INTO most_referred_pages (page_id, referred) VALUES(NEW.page_id, 1);
END;

CREATE TRIGGER refresh_mrp_on_delete AFTER DELETE ON referrer
FOR EACH ROW
BEGIN
        UPDATE most_referred_pages SET referred = referred - 1 WHERE page_id = OLD.page_id;
END;

INSERT OR IGNORE je rozšíření SQL jazyka v SQLite. Příkaz vloží hodnotu, pokud tomu nezabrání nějaké integritní omezení (např. unikátní hodnota na sloupci page_id). Pokud INSERT selže, chyba se ignoruje (takže trigger, potažmo INSERT, který trigger vyvolal, nebude odvolán).

Oracle

Vítejte v zemi materializovaným pohledům zaslíbené! Oracle už má podporu pro materializované pohledy dlouho a má jí velkou. Podívejte se na syntax pro vytvoření m. pohledu. Syntaxe je rozdělená do mnoha obrázků, aby se na stránku vůbec vešla! Já tu z toho nebudu probírat všechno, ale jen malou část:

CREATE MATERIALIZED VIEW view_name
[ BUILD {IMMEDIATE | DEFERRED} ]
[ REFRESH  [{ FAST | COMPLETE | FORCE }] [ON { COMMIT | DEMAND }]
           [ START WITH date ] [NEXT interval] ]
AS
SELECT ...;

Pro začátek začnu příkladem, kterým jsem začal v úvodní části o Postgresu. Vytvořím materializovaný pohled, který se ihned nezmaterializuje.

oracle> CREATE MATERIALIZED VIEW most_referred_pages
BUILD DEFERRED
AS
   SELECT referrer.page_id, count(*) AS referred
   FROM referrer
   GROUP BY referrer.page_id;

oracle> SELECT COUNT(*) FROM most_referred_pages;
COUNT(*)
--------
       0

BUILD DEFERRED je to, co způsobí, že se materializovaný pohled nematerializuje. Pokud tento výraz vynecháte (nebo nahradíte defaultním BUILD IMMEDIATE), SQL dotaz definující pohled se ihned provede a výsledek se uloží.

Oracle nemá REFRESH příkaz. Místo toho můžete použít balíček DBMS_MVIEW, konkrétně jeho proceduru REFRESH. Této procedůře můžete předat spousty parametrů, důležité jsou tyto dva:

ParametrVýznam
listŘetězec, čárkou oddělené jména m. pohledů, které chcete aktualizovat.
methodZpůsob aktualizace. f = FAST REFRESH, c = COMPLETE REFRESH, ? = FORCE REFERSH. Význam refresh metody popíši dále.

Procedůru můžete spustit dvěma způsoby, oba udělají totéž:

BEGIN
   DBMS_MVIEW.REFRESH (
       list   =>  'most_referred_pages',
       method =>  'c'
   );
END;
/
-- jinak zapsané volání téhož:
EXECUTE DBMS_MVIEW.REFRESH ( list =>  'most_referred_pages', method =>  'c');

Ověření, že to zabralo:

oracle> SELECT count(*) FROM most_referred_pages;
  COUNT(*)
----------
      2213

Balíček DBMS_MVIEW má i další užitečné procedůry, jako například REFRESH_ALL_MVIVEWS, která aktualizuje všechny materializované pohledy.

Fast, forced a complete refresh

Při vytváření pohledu můžete určit jeden ze tří způsobů refreshe: FAST, COMPLETE nebo FORCE.

REFRESH FAST
Pohled se bude aktualizovat jen podle změn od posledního refreshe v tabulkách, na kterých je pohled závislý. To je samozřejmě rychlejší, než když celý materializovaný pohled smažete a vytvoříte znova. Ale musíte mít někde zaznamenáno, co se změnilo. Takže tabulky, na kterých je pohled závislý (v oracle jim říkají master tables), musí své změny logovat.
REFRESH COMPLETE
Materializovaný pohled (resp. jeho data) je celý smazán a vytvořen znova. (Takhle se refreshují m. pohledy v Postgresu.)
REFRESH FORCE
Pokusí se provést FAST REFRESH. Pokud to nejde (třeba proto, že pro master tabulky neexistují logy), provede se REFRESH COMPLETE. Toto je defaultní volba.

Jako příklad zkusím vytvořit m. pohled pages_with_most_referrences s REFRESH FAST.

oracle> CREATE MATERIALIZED VIEW pages_with_most_referrences
REFRESH FAST
AS
   SELECT referrer_id, count(page_id) AS pages
   FROM referrer
   GROUP BY referrer_id;
ERROR at line 5:
ORA-23413: table "RIMMER"."REFERRER" does not have a materialized view log

Já to říkal. Pro REFRESH FAST musí mít master tabulky logování. Tak ho k tabulce referrer přidám. A to pomocí CREATE MATERIALIZED VIEW LOG. Timto příkazem můžete určit co všechno se bude logovat. Pro materializovaný pohled s agregacemi (jako třeba COUNT) musíte zahrnout do logování nové hodnoty (INCLUDING NEW VALUES).

oracle>  CREATE MATERIALIZED VIEW LOG ON referrer INCLUDING NEW VALUES;
ERROR at line 1:
ORA-00439: feature not enabled: Advanced replication

Tak bohužel, tahle funčknost není ve verzi XE dostupná, takže si to nevyzkoušíme. Ale prostě to někdy někde (v placené verzi) nějak funguje, takže 3x hurá Oracle :-).

Na REFRESH FAST pohledy jsou kladena určitá omezení pro to, z jakých SQL dotazů může být Fast Refresh View vytvořen.

Jestli máte náhodou placenou verzi, tak si pages_with_most_referrences zase smažte a jedeme dál…

DROP MATERIALIZED VIEW pages_with_most_referrences;

Automatický update

V MySQL jste viděli, jak se může vytvořit automaticky updatovaný pohled pomocí triggerů. V oracle můžete vytvořit materializovaný pohled, který tohle umí sám. Stačí uvést REFRESH ON COMMIT. Pak se pohled automaticky refreshne po commitnutých změnách na jeho master tabulkách.

CREATE MATERIALIZED VIEW pages_with_most_referrences
REFRESH ON COMMIT
AS
   SELECT referrer_id, count(page_id) AS pages
   FROM referrer
   GROUP BY referrer_id;

Pohled se vytvořil a naplnil daty. V databázi ovšem není ještě žádný záznam v tabulce referrer s referrer_id = 11976. Přesvědčíme se o tom na pohledu a pak tento záznam vložíme. Pak by se měl do pohledu automaticky promítnout.

oracle> SELECT * fROM pages_with_most_referrences  WHERE referrer_id IN (11976);

no rows selected
oracle> INSERT INTO referrer (id, referrer_id,page_id) VALUES (referrer_id_seq.NEXTVAL, 11976,11975);

oracle> SELECT * FROM pages_with_most_referrences WHERE referrer_id IN (11976);

no rows selected

Tedy až po commitu:

oracle> commit;

Commit complete

oracle> SELECT * FROM pages_with_most_referrences WHERE referrer_id IN (11976);

REFERRER_ID     PAGES
----------- ---------
      11976         1

Jupíí, funguje! Má to ale své mouchy. Pokud máte m. pohled s REFRESH ON COMMIT ale bez REFRESH FAST (jako v příkladu výše), tak se m. pohled při commitu změn v master tabulkách celý generuje znova. Což obvykle bývá velmi zdlouhavé (proto se m. pohledy vytvářejí v první řadě. (Na těchto testovacích datech to asi nepoznáte, protože tabulka referrer má jen 2500 záznamů a tak se m. pohled pages_with_most_referrences vygeneruje během zlomku sekundy).

Periodický update

Pomocí START WITH date a NEXT interval můžete zařídit, aby se m. pohled aktualizoval automaticky sám po určité době.

START WITH date určuje okamžik prvního updatu. Pokud jej neuvedete, ale uvedete NEXT interval, provede se první update okamžitě (stejně, jako když uvedete START WITH SYSDATE). Datum date musí být v budoucnosti.

NEXT interval určuje časový interval, po kterém se bude m. pohled aktualizovat. Pokud zadáte jako interval číslo, bude se interpretovat jako počet dní. 1 je jeden den, 2 jsou dva dny, 1/2 je 12 hodin atd.

Následující příklad vytvoří m. pohled most_referred_pages2 na základě m. pohledu most_referred_pages, ke kterému připojí tabulky host a page. Nastavím u něj, aby se aktualizoval každých 5 minut. A hned vyzkouším, jak to funguje.

CREATE MATERIALIZED VIEW most_referred_pages2 REFRESH START WITH SYSDATE NEXT
SYSDATE +1/(24*12)
AS
SELECT page.id AS page_id, page.schema, host.host, page.port, page.page, referred
FROM host
JOIN page ON host.id = host_id
JOIN most_referred_pages ON page.id = page_id
ORDER BY referred DESC;

Pohled je vytvořený. Podívejme se nejdřív, kolikrát je podle tohoto pohledu odkazovaná stránka s id 9764.

oracle> SELECT * FROM most_referred_pages2 WHERE page_id = 9764;
PAGE_ID SCHEMA        HOST PORT PAGE REFERRED
------- ------  ---------- ---- ---- --------
   9764   http  sallyx.org   80    /       2

2x. Tak vložím další odkaz. Pohled most_referred_pages2 je závislý na pohledu most_referred_pages, ten se ale periodicky neaktulizuje (ani automaticky ON COMMIT). Takže, aby se něco změnilo v most_referred_pages2, musím ho nejdřív aktualizovat ručně (ON DEMAND, řekl by oracle databázista).

oracle> INSERT INTO referrer (id, referrer_id, page_id) VALUES (referrer_id_seq.NEXTVAL, 9770, 9764);

1 row created.

oracle> COMMIT;

oracle> SELECT * FROM most_referred_pages WHERE page_id = 9764;

  PAGE_ID   REFERRED
---------- ----------
     9764          2

oracle> EXECUTE DBMS_MVIEW.REFRESH ( list =>  'most_referred_pages', method =>  'c');

PL/SQL procedure successfully completed.


oracle> SELECT * FROM most_referred_pages WHERE page_id = 9764;

  PAGE_ID   REFERRED
---------- ----------
     9764          3

oracle> SELECT * FROM most_referred_pages2 WHERE page_id = 9764;

  PAGE_ID    SCHEMA        HOST PORT PAGE REFERRED
---------- --------  ---------- ---- ---- --------
    9764       http  sallyx.org   80    /        2

Pohled most_referred_pages jsem aktualizoval, ale most_referred_pages2 je pořád beze změny. Stačí být ale trpělivý a chvilku si počkat (max. 5 minut).

-- po 5 minutách
oracle> SELECT * FROM most_referred_pages2 WHERE page_id = 9764;
  PAGE_ID    SCHEMA        HOST PORT PAGE REFERRED
---------- --------  ---------- ---- ---- --------
    9764       http  sallyx.org   80    /        3

A zase to vyšlo :-)

Závěr

Materializované pohledy jsou šikovná věc. Hodí se především tam, kde potřebujete mít rychle výsledek složitého dotazu, ale nepotřebujete mít aktuální data. Viděli jste, že můžete mít i věčně aktuální materialiozovaný pohled (ideálně REFRESH FAST ON COMMIT v Oracle, nebo tabulka + triggery). Pohledy se taky snadno používají pro jednoduchou replikaci dat, kdy si díky m. pohledu můžete z jedné databáze do druhé (třeba na svém notebooku) přenést jen určitý pohled na data (statistiky za poslední týden atp.).

V Postgresu jsou materializované pohledy novinkou, takže toho ještě moc neumí. (Krom toho co jsem zmínil v tomto tutoriálu snad stojí za zmínku už jen příkaz ALTER MATERIALIZED VIEW.

MySQL a SQLite m. pohledy nepodporují, ale dají se snadno nasimulovat.

Bezkonkurenčně nejlépe je na tom v současné době Oracle. Popsal jsem vám ty nejdůležitější atributy m. pohledů, které můžete nastavit. Kromě toho mají m. pohledy v oracle spoustu dalších vlastností (které můžete nastavovat např. pomocí ALTER MATERIALIZED VIEW. Představil jsem vám balíček DBMS_MVIEW. Kromě toho ale existuje i balíček BMS_REFRESH, který obsahuje další procedury pro práci s m. pohledy. Například vám umožní vytvořit skupiny materializovaných pohledů, které pak můžete refreshnout naráz (v jedné transakci).

V Oracle mohou být jednoduché m. pohledy updatovatelné (DML dotazy nad m. pohledem změní jeho master tabulku). On ten Oracle nebude zase až tak špatná databáze :-). A touto bombou se s vámi zde loučím.

PS: Pro hloubavé čtenáře tu ještě ukáži pár příkazů, které můžete použít pro replikaci dat z jedné databáze (rimmer) do druhé (petr).

oracle> connect system/system
oracle> GRANT CREATE DATABASE LINK to petr;
oracle> connect petr/petr
oracle> CREATE DATABASE LINK rimmer_link CONNECT TO rimmer IDENTIFIED BY rimmer USING 'xe';
oracle> SELECT count(*) FROM referrer@rimmer_link;
  COUNT(*)
----------
      2502
oracle> CREATE MATERIALIZED VIEW pages_with_most_referrences
AS
SELECT referrer_id, count(page_id) AS pages
FROM referrer@rimmer_link
GROUP BY referrer_id;

Materialized view created.
M. pohled vytvářený z tabulky z jiné databáze nemůže mít REFRESH ON COMMIT.
Komentář Hlášení chyby
Created: 10.10.2015
Last updated: 10.10.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..