Understanding Views

A view is a logical table that is based on one or more tables. The view itself contains no data. It is sometimes called a non-materialized view to distinguish it from a materialized view, which does contain data that has already been calculated from detail tables.

Views cannot be updated directly, but changes to the data in the detail tables are immediately reflected in the view.

A view is basically a SQL statement that is stored in a database which enables you to treat the results of a SQL query as a table itself. TimesTen generates the results of a view every time you query that view, so a view always has the latest data results. You can query a view just like you would query a regular table.

A materialized view enables you to precompute expensive SQL operations. There are cases where primarily read-only databases can increase the speed of their SELECT operations by precomputing the results of queries and storing them in a materialized view. The downside in this case is that the materialized view requires additional storage space. Keep in mind that when you perform DML operations on the detail tables of a materialized view, these DML operations are more resource intensive and more storage is required to store the materialized view rows. If you use too many materialized views on a table, this may reduce the performance of that table.

To perform any operation that creates, drops or manages a view, the user must have the appropriate privileges, which are described along with the syntax for all SQL statements in the SQL Statements chapter in the Oracle TimesTen In-Memory Database SQL Reference.

This section includes the following topics:

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.

Dropping a View

The DROP VIEW statement deletes the specified view.

The following statement drops the cust_order view.

Command> DROP VIEW cust_order;

Restrictions on Views and Detail Tables

There are certain restrictions on views and their detail tables.

When a view is referenced in the FROM clause of a SELECT statement, its name is replaced by its definition as a derived table during SQL compilation. The content of the derived table is said to be materialized, but this materialization is temporary and only exists for the duration of the SQL statement. For example, if both the view and the referencing select specify aggregates, the view is materialized before its result can be joined with other tables of the select.
A view cannot be dropped with a DROP TABLE statement. You must use the DROP VIEW statement.
A view cannot be altered with an ALTER TABLE statement.
Referencing a view can fail due to dropped or altered detail tables.