Question 7

Explanation

True options:

1. A view causes no storage space consumption – True

   • Regular views do not store data; they act as saved queries retrieving data dynamically from base tables. However, materialized views do consume storage since they store query results.

2. It is possible to modify the definition of a VIEW without dropping it using a CREATE OR REPLACE VIEW statement – True

   • The CREATE OR REPLACE VIEW statement allows updating a view’s definition without the need to drop and recreate it.

False options:

• A normal or regular VIEW can be indexed – False

  • Views cannot be indexed directly since they do not store data. Indexing applies only to underlying base tables.

• Tables in the defining query of a VIEW must always exist in order to create the VIEW – False

  • In Oracle SQL, using the FORCE option when creating a view allows its definition to be stored even if referenced base tables do not exist at the time.

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

Create VIEW

Syntax

The syntax for the CREATE VIEW Statement in Oracle/PLSQL is:

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

view_name - The name of the Oracle VIEW that you wish to create.

WHERE conditions Optional. - The conditions that must be met for the records to be included in the VIEW.

Example

Here is an example of how to use the Oracle CREATE VIEW:

CREATE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'Microsoft';

This Oracle CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the Oracle VIEW as follows:

SELECT *
FROM sup_orders;

Update VIEW

You can modify the definition of an Oracle VIEW without dropping it by using the Oracle CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the CREATE OR REPLACE VIEW Statement in Oracle/PLSQL is:

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  WHERE conditions;

view_name  -The name of the Oracle VIEW that you wish to create or replace.

A CHECK constraint allows you to specify a condition on each row in a table.

Note

A CHECK constraint can NOT be defined on a SQL View.

The CHECK constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.

A CHECK constraint can NOT include a SQL Subquery.

A CHECK constraint can be defined in either a SQL CREATE TABLE statement or a SQL ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement in Oracle is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
 
  ...
 
  CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
 
);

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts to rows for which the WHERE clause in the select_statement is not true. It also prevents updates to rows for which the WHERE clause is true but the update would cause it to be not true (in other words, it prevents visible rows from being updated to nonvisible rows).

In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. When neither keyword is given, the default is CASCADED. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well.

Views do not contain any data – it is just a stored query in the database that can be executed when called. All the data it shows comes from the base tables. One can think of a view as a virtual table or mapping of data from one or more

-A view takes up no storage space other than for the definition of the view in the data dictionary.

Views cannot have indexes associated as per Oracle SQL Standards.

An exception to needing a table to create a view is when Force is used. Force forces the creation of a View even when the View will be invalid. Views can be indexed if they are materialized view. This makes the statement Tables in the defining query of a VIEW must always exist in order to create the VIEW False and the statement VIEW can be indexed True. That said, a normal or regular view cannot be indexed.

https://docs.oracle.com/database/121/CCAPP/GUID-B66D7E02-D602-4B2A-89FA-B5A496702611.htm#CCAPP9037

CREATE FORCE VIEW for_view AS 
SELECT ttt 
FROM emp;

Warning: View created with compilation errors.

SELECT * 
FROM for_view;

ERROR at line 1:

ORA-04063: view "USER.FOR_VIEW" has errors

Types of Indexes for Materialized Views

The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements:

• Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, for best performance, create a single-column bitmap index on each materialized view key column.

• In the case of materialized views containing only joins using fast refresh, create indexes on the columns that contain the rowids to improve the performance of the refresh operation.

• If a materialized view using aggregates is fast refreshable, then an index appropriate for the fast refresh procedure is created unless USING NO INDEX is specified in the CREATE MATERIALIZED VIEW statement.

https://docs.oracle.com/cd/E29633_01/CDMOG/GUID-8D4FCC4B-39FF-464C-844E-7AD7E87190D3.htm