Wissensarchiv für Entwickler

Als „VIEW“ kann man prinzipiell eine Art virtuelle Tabelle verstehen, die durch ein gespeichertes SQL-Statement mit SELECT, JOIN, WHERE, ORDER BY etc. in Echtzeit definiert wird und die wie jede andere Tabelle im DBMS per SQL wieder abgefragt werden kann.

Ein einfaches Beispiel anhand von Produkten und Bestellungen die in aktuelle Käufe und höchsten Umsatz resultieren:

Tabellen anlegen

create table products (asin varchar(50), name varchar(250), price decimal(5,2));
create table orders (asin varchar(50), quantity integer, buy datetime);

Daten einpflegen

  INSERT INTO products(asin, name, price) VALUES
  ("A1", "Apfel", 1.23),
  ("B2", "Birne", 2.34),
  ("C3", "Orange", 3.45),
  ("D4", "Pflaume", 3.45);

  INSERT INTO orders(asin, quantity, buy) VALUES
  ("A1", 2, "2016-01-08 12:00:00"), -- ok
  ("B2", 3, "2016-01-07 12:00:00"), -- ok
  ("E5", 4, "2016-01-06 12:00:00"), -- no related product
  ("A1", 5, "2016-01-05 12:00:00"), -- out of time
  ("C3", 1, "2016-01-06 12:00:00")  -- insufficient quantity

Was steht drin?

  SELECT * FROM products

  SELECT * FROM orders

Eine View für Bestellungen ab dem 6.1.

  CREATE VIEW recentOrders AS
  SELECT *
  FROM orders
  INNER JOIN products USING(asin)
  WHERE
  	buy >= "2016-01-06 12:00:00"
  	AND quantity > 1
  ORDER BY
  	buy ASC,
  	quantity DESC

  SELECT * FROM recentOrders

Ergebnis:

Für C, D, und E sind keine Ergebnisse vorhanden, da kein entsprechendes Produkt vorlag, die Bestellung älter als einen Tag oder die Stückzahl zu klein war.

Und hier eine View für Artikel mit höchsten Umsatz:

  CREATE VIEW highSalery AS	
  SELECT
	p.*,
	COUNT(o.asin) AS sold,
	COUNT(o.asin) * price AS total
  FROM products p
  LEFT JOIN orders o USING(asin)
  GROUP BY asin
  ORDER BY total DESC

  SELECT * FROM highSalery

Ergebnis:

Bei einer „MATERIALIZED VIEW“ handelt es sich um eine Art Cache zur Performanceoptimierung. Im Gegensatz zur View wird hier das Ereignis, das zum Aktualisierung der Ergebnisse führt, vom Client in die Datenbank selbst verlegt:

• Bei der Abfrage einer View durch den Client werden die geforderten Daten aus den einzelnen Tabellen und Relationen zusammengetragen und zurückgeliefert, der Trigger liegt auf der Abfrage der Sicht
• Beim Abruf der Materialized View werden die Daten aus dem Cache zurückgeliefert. Der Cache wird erst durch den Server aktualisiert, sobald Daten geändert werden (Update). Die Last entsteht ausschliesslich bei Schreiboperationen.

Um eine Materialized View anzulegen braucht man nur das entsprechende Schlüsselwort voranzustellen, z.B.:

  CREATE materialized VIEW recentOrders AS ...

danach kann man die View durch beliebige Trigger auf involvierte Relationen aktualisieren

  CREATE TRIGGER materialize after INSERT OR UPDATE OR DELETE ON orders EXECUTE PROCEDURE materialize();