Mi a nézetek haszna?

  • A kódújrafelhasználásnak SQL-ben szinte az egyetlen módja az, hogy nézeteket hozunk létre. Ha gyakran végrehajtunk egy lekérdezést (vagy nagyobb lekérdezéseken belül egy subquery-t), érdemes lehet azt nézetként menteni.
  • Elrejthető velük az adatmodellből fakadó komplexitás. Egy nézet felhasználójának nem kell tudnia, hogy milyen alaptáblákból származnak az eredmények, azok milyen kapcsolatban állnak egymással. (Figyelem, ez egy kétélű kard!)
  • Segíthetnek az adatmodell refaktorálásában. Ha átneveztünk egy táblát vagy megváltoztattuk a mezőit, elég lehet létrehozni egy nézetet, amely úgy néz ki, mint a régi tábla. Így a régi táblát használó lekérdezések továbbra is működnek.
  • Absztrakciós réteg hozható létre velük. Egy alkalmazás és az adatbázis táblái közé beiktathatunk nézeteket, például azért, hogy homogenizáljuk a táblák összevissza elnevezését és a mezőneveiket, vagy azért, mert bizonyos táblákat csak másokkal kötve érdemes használni.
  • Javíthatnak a teljesítményen. Ha egy tábla mezőinek csak egy részét használjuk, akkor ne kérdezzük le mindet, iktassunk be inline view-t. Egyáltalán nem mindegy, mennyi adatot mozgatunk. Ha számít egy lekérdezés futásideje, és optimálással már nem javítható tovább, akkor hozzunk létre materialized view-t az eredmények tárolására.
  • Adatbiztonsági megoldásként is használhatók. Letrehozható nézet, amely csak bizonyos mezőit mutatja egy olyan táblának, amelyet a nézethez hozzáférő felhasználó nem láthat. Ezáltal a nézettel szabályozhatjuk, mit lát valaki egy táblából.

View

A normál nézetekre tekinthetünk tárolt lekérdezésként vagy virtuális táblaként vagy absztrakciós eszközként. Mivel adatokat nem tartalmaznak, alig foglalnak helyet, így akár tömegesen is alkalmazhatók.

Oracle:

-- Ha egy nézet csak egy alaptáblára épül és tartalmaz minden
-- nem nullázható mezőt, akkor DML is végezhető rajta (updatable).
-- Ezt általában nem akarjuk, ezért kell a with read only.
create or replace view myview
as
select col1, col2, col3
from mytable
with read only
;
-- Így nézhetjük meg a tárolt lekérdezést:
select
  v.owner, v.view_name, v.text,
  v.read_only
from all_views v
where v.view_name = 'MYVIEW'
;
-- Így törölhetjük a nézetet:
drop view myview
;

PostgreSQL:

-- Ha egy nézet csak egy alaptáblára épül és tartalmaz minden
-- nem nullázható mezőt, akkor DML is végezhető rajta (updatable).
create or replace view myview
as
select col1, col2, col3
from mytable
;
-- Így nézhetjük meg a tárolt lekérdezést:
select
  v.table_schema, v.table_name, v.view_definition,
  v.is_updatable
from information_schema.views v
where v.table_name = 'myview'
;
-- Így törölhetjük a nézetet:
drop view myview
;

Materialized view

A mat. view a lekérdezés kódja mellett az eredményét is tárolja. Költséges lekérdezések gyorsítótárazására való. Rendszeres frissítést igényel. Ha egy alaptáblában adatváltozás történik, akkor stale állapotba kerül.

Oracle:

-- Megadhatjuk, hogy az első töltés a létrehozáskor (build immediate)
-- vagy később, az első frissítéskor (build deferred) történjen.
-- Megadhatjuk, hogy csak kérésre frissüljön (refresh on demand)
-- vagy akkor, ha egy alaptábla változik (refresh on commit).
create materialized view mymview
build immediate
refresh on demand
as
select col1, col2, col3
from mytable
;
-- Nincs DDL a mat. view frissítésére. De általában amúgy is
-- időzített kódhívással akarjuk frissteni.
exec dbms_mview.refresh('MYMVIEW','C')
;
-- Így nézhetjük meg a tárolt lekérdezést és a mat. view állapotát.
-- A leggyakoribb állapotok: fresh vagy stale.
select
  mv.owner, mv.mview_name, mv.query,
  mv.last_refresh_date, mv.staleness
from all_mviews mv
where mv.mview_name = 'MYMVIEW'
;
-- Így törölhetjük a mat. view-t:
drop materialized view mymview
;

PostgreSQL:

-- Megadhatjuk, hogy az első töltés a létrehozáskor (with data)
-- vagy később, az első frissítéskor (with no data) történjen.
create materialized view mymview
as
select col1, col2, col3
from mytable
with data
;
-- DDL a mat. view frissítésére:
refresh materialized view mymview
;
-- Így nézhetjük meg a tárolt lekérdezést.
-- A mat. view-knak nincs külön nézete az information_schema-ban.
-- Nincs állapotuk, se utolsó frissítési időpontjuk.
select
  mv.schemaname, mv.matviewname, mv.definition
from pg_matviews mv
where mv.matviewname = 'mymview'
;
-- Így törölhetjük a mat. view-t:
drop materialized view mymview
;

Inline view

Ezek nem különálló objektumok, hanem lekérdezésekben táblák helyén előforduló subquery-k. Áttekinthetőbbé vagy hatékonyabbá tehetik a lekérdezést. Szinte mindig explicit mezőfelsorolást érdemes használni (a jobb áttekinthetőségre és a minimális adatmozgatásra való törekvés miatt), ami inline view-k megjelenésével jár.

Az inline view-k többször használhatóvá válnak egy lekérdezésen belül az ún. subquery factoring által, ekkor a nevük common table expression.

with
cte1 as (select col1, col2 from mytable1),
cte2 as (select col3, col4 from mytable2)

select cte1.col2, cte2.col4
from cte1
inner join cte2 on cte1.col1 = cte2.col3
;