Creating a View

To create a view, use the CREATE VIEW SQL statement.

The syntax for all SQL statements is provided in the SQL Statements chapter in the Oracle TimesTen In-Memory Database SQL Reference. 

CREATE VIEW ViewName AS SelectQuery;

This selects columns from the detail tables to be used in the view.

For example, create a view from the table t1: 

Command> CREATE VIEW v1 AS SELECT * FROM t1;

Now create a view from an aggregate query on the table t1: 

Command> CREATE VIEW v1 (max1) AS SELECT max(x1) FROM t1;

The SELECT Query in the CREATE VIEW Statement

The SELECT query used to define the contents of a materialized view is similar to the top-level SQL SELECT statement.

The SELECT query used to define the contents of a matierialized view has the following restrictions:

  • A SELECT * query in a view definition is expanded at view creation time. Any columns added after a view is created do not affect the view.

  • The following cannot be used in a SELECT statement that is creating a view:

  • DISTINCT

  • FIRST

  • ORDER BY

  • Arguments

  • Temporary tables

  • Each expression in the select list must have a unique name. A name of a simple column expression would be that column's name unless a column alias is defined. RowId is considered an expression and needs an alias.

  • No SELECT FOR UPDATE or SELECT FOR INSERT statements can be used on a view.

  • Certain TimesTen query restrictions are not checked when a non-materialized view is created. Views that violate those restrictions may be allowed to be created, but an error is returned when the view is referenced later in a processed statement.

See SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference.