Materializované pohledy
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 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);
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);
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);
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;
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.
Protože jsem použil WITH NO DATA
, bude pohled prázdný, resp. neinicializovaný.
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.
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:
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 :-).
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.
LIMIT 0
tu funguje jako WITH NO DATA
.
Místo příkazu REFRESH MATERIALIZED VIEW
můžete použít tyto příkazy:
Ještě vytvořím tabulce indexy, které si zaslouží:
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_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.
Co se plnění týče, je to stejné jako u MySQL, jen SQLite nepodporuje LOCK TABLE
:
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_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:
[ 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.
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:
Parametr | Význam |
---|---|
list | Řetězec, čárkou oddělené jména m. pohledů, které chcete aktualizovat. |
method | Způ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éž:
Ověření, že to zabralo:
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
.
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
).
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…
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.
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.
Tedy až po commitu:
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.
Pohled je vytvořený. Podívejme se nejdřív, kolikrát je podle tohoto pohledu odkazovaná stránka s id 9764.
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).
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).
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> 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.