CREATE VIEW

Documentation

VoltDB Home » Documentation » Using VoltDB
< previoustable of contentsnext >

CREATE VIEW

CREATE VIEW — Creates a view into one or more tables, optimizing access to a summary of their contents.

Synopsis

CREATE VIEW view-name ( view-column-name [,...] )
AS SELECT { column-name | selection-expression } [AS alias] [,...]
FROM table-reference [join-clause...]
[WHERE [NOT] boolean-expression [ {AND | OR} [NOT] boolean-expression]...]
[GROUP BY { column-name | selection-expression } [,...]]

table-reference:
{ table-name [AS alias] }

join-clause:
, table-reference
[INNER] JOIN [{table-reference}] [join-condition]

join-condition:
ON conditional-expression
USING (column-reference [,...])

Description

The CREATE VIEW statement creates a view of a table, a stream, or joined tables with selected columns and aggregates. VoltDB implements views as materialized views. In other words, the view is stored as a special table in the database and is updated each time the corresponding database contents are modified. This means there is a small, incremental performance impact for any inserts or updates to the tables, but selects on the view will execute efficiently.

The following limitations are important to note when using the CREATE VIEW statement with VoltDB:

  • If the SELECT statement contains a GROUP BY clause, all of the columns and expressions listed in the GROUP BY must be listed in the same order at the start of the SELECT statement. Aggregate functions, including COUNT(*), are allowed following the GROUP BY columns.

  • Views are allowed on individual tables or streams, or joins of multiple tables. Joining streams is not supported.

  • Joins must be inner joins and cannot be self-joins. All other limitations for joins as described in the SELECT statement also apply to joins in views.

  • Views that join multiple tables must include a COUNT(*) field listed after all GROUP BY columns.

  • To avoid performance problems when inserting data into a view that joins multiple tables, it is strongly recommended you define indexes on the table columns involved in the join.

Examples

The following example defines a view that counts the number of records for a specific product item grouped by its location (that is, the warehouse the item is in).

CREATE VIEW inventory_count_by_warehouse (
     productID,
     warehouse,
     total_inventory
) AS SELECT
     productID,
     warehouse,
     COUNT(*)
FROM inventory GROUP BY productID, warehouse;

The next example uses a WHERE clause but no GROUP BY to provide a count and minimum and maximum aggregates of all records that meet a certain criteria.

CREATE VIEW small_towns ( number, minimum, maximum )
    AS SELECT count(*), min(population), max(population) 
    FROM TOWNS WHERE population < 10000;

The final example demonstrates joining two tables in a view. This definition provides a similar view to the first example, except it uses the productID column to join two tables, Product and Inventory:

CREATE VIEW inventory_count_by_warehouse (
     productName,
     warehouse,
     total_inventory
) AS SELECT
     product.productName,
     inventory.warehouse,
     COUNT(*)
FROM product JOIN inventory 
     ON product.productID = inventory.productID
     GROUP BY product.productName, inventory.warehouse;
< previoustable of contentsnext >