FrançaisGet started

Views

CREATE VIEW and DROP VIEW: a view as a stored query and not a copy, its uses (simplify, protect, isolate from the schema), updatable or not, WITH CHECK OPTION and the materialized view.

  • 22 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • View: what is it?

    Show the answer

    A SELECT stored in the database under a name: you query it like a table, but it stores no row; its result is recomputed at each read.

    CREATE VIEW v_sf AS SELECT … FROM film WHERE genre = 'SF': after an INSERT into film, a new SF film shows up in v_sf right away, with nothing to synchronise.

  • The film table holds three SF films: Dune, Alien and Matrix. What does the last SELECT return?

    ```sql
    CREATE VIEW v_sf AS
    SELECT titre FROM film WHERE genre = 'SF';
    INSERT INTO film VALUES (7, 'Interstellar', 'SF', 169, 2014);
    SELECT titre FROM v_sf;
    `

    • The four SF films, Interstellar included
    • The three SF films from before the INSERT
    • An error: the table changed under the view
    • Only Interstellar, the added row
    Show the answer

    The four SF films, Interstellar included

    The view is only a query: SELECT titre FROM v_sf replays its SELECT on film, INSERT included. No copy has to be redone.

  • A view stores only …: the rows are read from … at each query.

    Show the answer

    A view stores only its query: the rows are read from the base tables at each query.

    That is why a view is always up to date and takes almost no space. It is also why a heavy view is recomputed at each read.

  • Materialized view: what is it?

    Show the answer

    A query whose result is stored on disk like a table: reading is fast, but the data only catches up with the base tables when it is refreshed.

    In PostgreSQL: CREATE MATERIALIZED VIEW …, then REFRESH MATERIALIZED VIEW … to recompute. You gain speed and lose freshness.

  • Five reports copy the same 40-line query (three joins and a margin calculation). The calculation rule has just changed. What should you do so you never have to fix it in five places again?

    • Copy its result into a table, to be refreshed by hand every week
    • Store it in a view read by the five reports
    • Replace the joins with a SELECT * to shorten the copied query
    • Give the query the same alias in each of the five reports that copy it
    Show the answer

    Store it in a view read by the five reports

    A view gives the query a single name: the rule is written once, and the five reports read it like a table. A copy in a table drifts as soon as the data changes.

Study this deck