12.1 Simple vs Complex Views

12.1 Simple vs Complex Views

In relational database management systems, direct access to physical base tables is often neither secure nor convenient for end users and application developers. A View is a logical, named representation of a subset of data or a pre-defined relationship across one or more physical tables. In Oracle SQL, a view does not store physical data rows of its own (with the exception of specialized materialized views); instead, it stores a compiled SQL SELECT statement within the Oracle data dictionary.

When a user queries a view, the Oracle Database engine transparently retrieves the view definition from the data dictionary, merges the view's query with the user's outer query, optimizes the combined execution plan, and retrieves the underlying rows directly from the physical base tables.

---

Architecture and Purposes of Database Views

Views serve as virtual windows onto physical data structures. Understanding the four core architectural benefits of views is essential for the 1Z0-071 examination:

+-------------------------------------------------------------------------+
|                        ORACLE VIEW ARCHITECTURE                         |
+-------------------------------------------------------------------------+
|                                                                         |
|  USER / APPLICATION QUERY:                                              |
|  SELECT employee_name, department_name, annual_salary                    |
|  FROM emp_dept_summary_v                                                |
|  WHERE annual_salary > 75000;                                           |
|                              |                                          |
|                              v                                          |
|  DATA DICTIONARY (USER_VIEWS):                                          |
|  [ Stored SQL Query Text: SELECT e.first_name || ' ' || e.last_name... ]|
|                              |                                          |
|                              v (Query Merging & Optimization)           |
|  ORACLE SQL ENGINE / PARSER:                                            |
|  Executes unified execution plan against physical storage               |
|                              |                                          |
|             +----------------+----------------+                         |
|             |                                 |                         |
|             v                                 v                         |
|  BASE TABLE: EMPLOYEES          BASE TABLE: DEPARTMENTS                 |
|  [ Physical Data Blocks ]       [ Physical Data Blocks ]                |
|                                                                         |
+-------------------------------------------------------------------------+

1. Data Security and Granular Access Control

By exposing views rather than base tables, database administrators can enforce column-level and row-level security. Sensitive columns (such as social security numbers, banking details, or executive salaries) can be omitted from the view projection. Furthermore, a WHERE clause embedded within the view restricts users to rows within their own department or geographical region without needing custom application-level filtering.

2. Simplifying Complex Queries

Views encapsulate elaborate business logic, multi-table joins, nested subqueries, and complex mathematical or single-row expressions into a clean, reusable interface. Downstream report developers and business analysts can query SELECT * FROM sales_summary_v without needing to construct a 40-line, 6-table join query.

3. Logical Data Independence

Views insulate client applications from underlying database schema refactoring. If a DBA splits a table into two normalized tables, renames physical columns, or redesigns indexes, the existing views can be re-created with CREATE OR REPLACE VIEW to present the exact legacy column names and structures. Application code continues running seamlessly without code refactoring.

4. Customizing and Standardizing Data Presentation

Views provide standardized column aliases, pre-formatted strings, and consistent business metrics (such as calculating net margin or formatting phone numbers) across an entire organization.

---

Syntax of the CREATE VIEW Statement

The full syntax for creating a view in Oracle SQL includes several critical optional clauses and directives:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
    [(column_alias_1 [, column_alias_2, ...])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]];

Syntax Elements Breakdown

---

Simple Views vs. Complex Views

Oracle SQL categorizes views into Simple Views and Complex Views based on the structural characteristics of the underlying SELECT subquery. This categorization dictates whether and how data manipulation (DML) can be performed.

Exhaustive Comparison Matrix

Code Examples

1. Simple View Example

A simple view queries a single table and projects base columns with basic row filtering:

-- Create a simple view on the employees table for department 50
CREATE OR REPLACE VIEW emp_dept50_v AS
SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary
FROM employees
WHERE department_id = 50;

2. Complex View Example

A complex view joins multiple tables, applies group functions, and calculates aggregations:

-- Create a complex view displaying department-level salary analytics
CREATE OR REPLACE VIEW dept_salary_analytics_v AS
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS total_staff,
    MIN(e.salary)        AS lowest_salary,
    MAX(e.salary)        AS highest_salary,
    ROUND(AVG(e.salary), 2) AS average_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

---

View Column Aliasing Rules & Error Conditions

When creating a view, columns can derive their names from base table column names or from explicit aliases. Oracle offers two distinct mechanisms for assigning column aliases to views:

Method 1: Aliasing in the SELECT Subquery List

Aliases can be specified directly on the projection items using the AS keyword or whitespace:

CREATE OR REPLACE VIEW sales_reps_v AS
SELECT 
    employee_id AS rep_id,
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_compensation
FROM employees
WHERE job_id = 'SA_REP';

Method 2: Aliasing in the View Header Declaration

Aliases can be listed in parentheses immediately following the view name:

CREATE OR REPLACE VIEW sales_reps_v (
    rep_id,
    full_name,
    annual_compensation
)
AS SELECT 
    employee_id,
    first_name || ' ' || last_name,
    salary * 12
FROM employees
WHERE job_id = 'SA_REP';

Critical Exam Rules for Column Aliasing

1. Mandatory Aliases on Computed Expressions (ORA-00998): If a column in the SELECT list is derived from an expression, function, concatenation operator, or literal value, it must be given an explicit alias (either inline or in the header). Failure to do so causes compilation failure:

   -- ILLEGAL: Expression 'salary * 12' lacks an alias
   CREATE VIEW bad_view AS SELECT employee_id, salary * 12 FROM employees;
   -- ORA-00998: must name this expression with a column alias
   

2. Header Alias Count Matching (ORA-01730): If aliases are declared in the view header, the number of aliases in the header list must exactly equal the number of expressions in the SELECT subquery projection list:

   -- ILLEGAL: Header declares 3 aliases, but SELECT list projects only 2 columns
   CREATE VIEW bad_count_v (emp_id, emp_name, emp_sal) AS
   SELECT employee_id, last_name FROM employees;
   -- ORA-01730: invalid number of column names specified
   

---

The FORCE Keyword and Object Lifecycle

By default, Oracle executes CREATE VIEW with NOFORCE. If the underlying tables do not exist, or if the executing schema lacks SELECT privileges on those tables, Oracle refuses to create the view and raises an error (ORA-00942: table or view does not exist).

When FORCE is specified, Oracle creates the view entry in the data dictionary regardless of whether the base tables or privileges exist.

-- Create a view on a base table that has not yet been created
CREATE FORCE VIEW future_orders_v AS
SELECT order_id, customer_id, order_total
FROM non_existent_orders_table;
-- Result: View created with compilation errors.

Lifecycle of a FORCE View in the Data Dictionary

+-------------------------------------------------------------------------+
|                        FORCE VIEW COMPILATION LIFECYCLE                 |
+-------------------------------------------------------------------------+
|                                                                         |
|  1. CREATE FORCE VIEW future_v AS SELECT ... FROM missing_table;        |
|     - View metadata is stored in USER_VIEWS                             |
|     - USER_OBJECTS.STATUS = 'INVALID'                                   |
|                                                                         |
|  2. User queries invalid view: SELECT * FROM future_v;                  |
|     - ORA-04063: view "SCHEMA.FUTURE_V" has errors                      |
|                                                                         |
|  3. DBA creates base table: CREATE TABLE missing_table (...);           |
|                                                                         |
|  4. User queries view again: SELECT * FROM future_v;                    |
|     - Oracle detects valid base table                                   |
|     - Oracle automatically recompiles the view                          |
|     - USER_OBJECTS.STATUS becomes 'VALID'                               |
|     - Query returns rows successfully!                                  |
|                                                                         |
+-------------------------------------------------------------------------+

Exam Tip: Why use FORCE? In large enterprise database deployments, schema deployment scripts often contain circular dependencies (e.g., View A references View B, which references View A's schema). Using FORCE allows all view definitions to be compiled first without failing due to script execution order.

---

Dropping Views and Data Independence Verification

A view is dropped from the database using the DROP VIEW DDL statement:

DROP VIEW emp_dept50_v;

Rules for Dropping Views:

• No Base Table Impact: Dropping a view removes only the view's definition from USER_VIEWS and drops any object privileges specifically granted on that view. It has zero effect on the physical base tables, column definitions, constraints, or stored table data.
• No Cascade Required: Unlike dropping a table that has dependent foreign keys (which may require CASCADE CONSTRAINTS), dropping a view does not require a cascade clause. Any other views that depended on the dropped view simply become INVALID in USER_OBJECTS.
• Inverse Action: Conversely, if a physical base table is dropped (DROP TABLE employees), any views built upon employees remain in the data dictionary but their status changes to INVALID.