Example create materialized view

Fiche express
Type	Script SQL*Plus d'exemple
Domaine	Exemple DDL
Voir aussi	Example create dimension · Example create emp dept custom

Example create materialized view donne deux exemples de vues matérialisées avec rafraîchissement complet à chaque COMMIT et réécriture de requête activée (ENABLE QUERY REWRITE) : une agrégation de chiffre d'affaires par avion, et une agrégation de salaire moyen par département sur le schéma emp/dept (voir Example create emp dept custom).

Pour que ENABLE QUERY REWRITE soit effectivement exploité par l'optimiseur, la base doit avoir QUERY_REWRITE_ENABLED = TRUE et, idéalement, des objets DIMENSION déclarant les hiérarchies d'agrégation utilisables — voir Example create dimension.

Script

La seconde vue matérialisée (salaire moyen par département) contenait un bug dans le script d'origine : la jointure emp/dept ne portait aucune condition de jointure (produit cartésien), et la colonne était nommée AVG_MONTHLY_SALARY sans qu'aucune agrégation ne soit réellement calculée. Corrigé ci-dessous avec la condition de jointure sur dept_id et un véritable AVG() groupé par département.

-- Vue matérialisée : chiffre d'affaires total par avion
CREATE MATERIALIZED VIEW flight_fact_mv
  BUILD IMMEDIATE
  REFRESH COMPLETE ON COMMIT
  ENABLE QUERY REWRITE
AS
  SELECT
      plane_id          plane_id
    , SUM(sale_amount)  sum_sale_amount
  FROM   scott.flight_fact
  GROUP BY plane_id
/

-- Vue matérialisée : salaire mensuel moyen par département
CREATE MATERIALIZED VIEW monthly_salary_mv
  BUILD IMMEDIATE
  REFRESH COMPLETE ON COMMIT
  ENABLE QUERY REWRITE
AS
  SELECT
      b.name                    dept_name
    , AVG(a.monthly_salary)     avg_monthly_salary
  FROM
      emp a, dept b
  WHERE
      a.dept_id = b.dept_id
  GROUP BY
      b.name
/

Voir aussi

• Example create dimension — hiérarchie de dimension exploitable par la réécriture de requêtes
• Example create emp dept custom — schéma emp/dept source de la seconde vue