SQL View Inner Workings

Calling a view is no different from calling a table: a SELECT statement naming the view is all it takes. That simplicity is deliberate; a view exists specifically so that whoever calls it doesn't need to think about anything beyond a name and a SELECT. Everything genuinely interesting about a view happens after that call is issued, not in how the call itself is written, which is exactly what this lesson focuses on.

Suppose a view named EmployeeView already exists. Retrieving everything from it is:

SELECT * FROM EmployeeView;

The asterisk pulls every column, which is fine for quick exploration but rarely the right choice in production code, both for clarity (a reader has to go look up what EmployeeView actually contains) and for performance (the database has to resolve and return columns nobody asked for). Naming only the columns actually needed is almost always the better habit:

SELECT EmployeeID, EmployeeName, Department
FROM EmployeeView;

Nothing about calling a view requires special syntax beyond this. Whatever a view is named, EmployeeView, MyView, utah_customers from earlier lessons, referencing that name in a SELECT is the entire mechanism:

SELECT * FROM MyView;

Suppose MyView's underlying query is the same Utah-filtering example from the previous lesson:

SELECT * FROM MyTable
WHERE State = 'UT';

Calling MyView and calling that raw query produce identical results, which is exactly the point: once the logic is saved under a name, nobody calling the view needs to know, or care, what that underlying query actually says.

It's common to describe a view, in general database theory, as something the engine "recreates" each time it's called: the view has no data of its own, so calling it supposedly triggers the underlying query to run and rebuild a temporary result table that then gets used and discarded. That's a reasonable mental model for understanding views in the abstract, but it's not quite how Oracle specifically handles the call, and since this lesson is about inner workings, it's worth being precise rather than settling for the simplified version.

When a SQL statement references a view, Oracle performs three real steps:

1. Merges the query against the view with the view's own defining query, whenever possible. Rather than running the view's query first and then filtering that separate result, Oracle combines the calling statement and the view's definition into a single query, and optimizes that combined query as if the view had never been involved. This means Oracle can use indexes on the base table's columns even when the calling statement only ever references the view by name, not the underlying table directly.
2. Parses the merged statement in a shared SQL area. Oracle only creates a new shared SQL area for a statement that calls a view if no existing shared SQL area already holds a similar statement. Different sessions calling the same view with similar queries can reuse that parsed representation rather than each paying the full parsing cost separately, which is where views deliver a genuine memory benefit beyond convenience.
3. Executes the resulting statement.

Here's what that merging looks like concretely. Suppose a view joins two tables and filters to one department:

CREATE VIEW employees_view AS
SELECT employee_id, last_name, salary, location_id
FROM employees JOIN departments USING (department_id)
WHERE department_id = 10;

A call against that view for one specific employee:

SELECT last_name
FROM employees_view
WHERE employee_id = 200;

Oracle doesn't run the view's query, get a result, and then filter that result by employee_id as a separate second step. It merges the two statements into one, equivalent to writing this directly:

SELECT last_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
  AND departments.department_id = 10
  AND employees.employee_id = 200;

Notice what this merged form makes possible that a two-step "compute the view, then filter it" approach wouldn't: the employee_id = 200 condition is now sitting in the same query as the join itself, where an index on employees.employee_id can be used directly to locate the one row that matters, rather than the database first materializing every row in department 10 and only then scanning that intermediate result for employee_id = 200. That's the concrete payoff behind the abstract claim that Oracle "can use indexes on any referenced base table columns" once merging happens: the optimizer gets to see the whole picture, the view's logic and the calling query's condition together, and choose an access path informed by both at once.

This is why a view's data is always current without anything resembling a "refresh" happening behind the scenes: there's no cached result sitting around waiting to go stale. Each call merges fresh into a query that reads the base tables directly, at the moment it runs.

Merging is the default, not an absolute guarantee. Oracle sometimes can't merge a view's definition into the calling query, and in those cases it may not be able to use every index on the referenced columns the way the merged version would have allowed.

One concrete, deliberate example of this: a view whose defining query carries a RESULT_CACHE hint is specifically excluded from merging. Caching a view's result only makes sense if that cached result can be reused as-is across multiple calls, and merging would defeat that entirely by folding the view's logic into whatever different calling query happens to reference it each time. Oracle's own documentation states this plainly: a caching view is not merged into its outer query block. The trade-off is explicit: you gain a reusable cached result, at the cost of the calling query no longer being able to push its own conditions down into the view's execution the way a merged query normally would.

This is worth knowing precisely because it cuts against a natural assumption that merging always happens and is always the best outcome. Sometimes deliberately preventing it, as with a RESULT_CACHE view, is the correct performance choice for a specific workload; the general default of merging remains the right behavior for the vast majority of ordinary views.

Step two of the process, parsing the merged statement in a shared SQL area, is where views deliver a real benefit beyond convenience. Oracle only builds a new shared SQL area for a statement calling a view if no existing shared SQL area already holds a similar statement. Once a statement has been parsed and placed in the shared pool, it stays there, available for reuse, until Oracle needs the space and evicts it using a least-recently-used algorithm; items used by many sessions tend to stick around precisely because they keep getting reused, which minimizes the overhead of repeatedly parsing the same or similar SQL.

Concretely: if fifty different application sessions all query employees_view with a similar shape of statement, Oracle doesn't parse that statement fifty separate times. The first session to run it pays the parsing cost; the other forty-nine can reuse that already-parsed representation. This is a genuine memory and CPU savings that has nothing to do with the view's data being cached, it's the parsed statement being shared, not the result. A view's result is never reused this way; only the parsed SQL behind repeated similar calls to it is.

That distinction is worth sitting with for a moment, since it's easy to blur the two. Nothing about shared SQL area reuse means fifty sessions see the same rows; each execution of the merged, shared statement still reads whatever the base tables currently contain, independently. What's shared is the work of understanding the statement's structure, its parse tree, its optimized execution plan, not any of the data that statement subsequently retrieves. Two sessions can share the exact same parsed representation of a query against employees_view and still see completely different result sets if the underlying employees and departments tables have changed between the two executions.

The "recreate a temporary table, then discard it" description also implies something Oracle specifically doesn't do: allocate a distinct, named view-table object that lives in memory for the duration of one statement and then vanishes. Because Oracle merges the view into the calling query whenever it can, there's typically no separate view-shaped object in memory at all, just the single merged statement's own execution, operating on rows from the base tables the same way any other query would. The "virtual table that exists only transiently" framing is a useful way to talk about views conceptually, but the actual mechanism underneath it, in Oracle specifically, is query merging and shared SQL reuse, not a table that gets built and torn down on every call.

This ties directly back to the very first characteristic established in this module: a view is a stored query, not stored data. Everything covered in this lesson is really just an elaboration of that one sentence, applied specifically to what happens the instant a SELECT names a view.

Some tools let a user save the result of a view as a genuine, separate base table, effectively freezing a snapshot of whatever the view returned at that moment. This is worth actively avoiding. A saved snapshot like this has no mechanism to stay current; the moment the original tables change, that saved copy is simply wrong, with nothing about it flagging that it's gone stale.

Concretely, imagine someone exports utah_customers into a new table called utah_customers_snapshot for a one-off analysis. Six months later, another analyst finds that table, assumes it's still accurate because nothing about it looks unusual, and builds a report on it. Every customer who's moved out of Utah since the snapshot was taken is still sitting in that report as if they were current; every new Utah customer since then is simply missing. Nothing about querying utah_customers_snapshot produces an error or a warning; it just quietly returns wrong answers with complete confidence.

This is a different thing entirely from a materialized view, which uses its own data structure specifically designed to store a view's result and refresh it on a defined schedule. A materialized view is a deliberate, managed trade of currency for speed; an ad hoc "save the view's output as a table" action is neither managed nor refreshed, and the resulting staleness tends to go unnoticed until someone acts on data that's long since changed underneath them.

Everything covered so far has focused on SELECT, but the same underlying mechanism, referencing the view by name, extends to INSERT, UPDATE, and DELETE against views that support them. The updatability rules covered in an earlier lesson, primary keys, NOT NULL columns, single-table versus join views, still determine whether a given write is even legal; what this lesson adds is that when a write against an updatable view is legal, Oracle applies the same merging principle to translate it into an operation against the actual base table. There's no version of "recreate the view, modify it, then somehow propagate that back to the base table" happening; the write against the view is translated into a write against the base table the view is built from, directly, since that's the only place any data ever physically exists.

Looking Ahead

Calling a view is syntactically nothing more than a SELECT against a name. What happens after that call, query merging, shared SQL area reuse, execution against the current base tables, is what actually guarantees the "always current" behavior views are known for, rather than any kind of caching or table recreation. Keeping that distinction straight is what separates knowing how to use a view from understanding why it behaves the way it does.

A few points worth carrying forward from this lesson:

• Calling a view requires no special syntax beyond an ordinary SELECT naming it, exactly like querying a table.
• Oracle's real mechanism is merge, parse in a shared SQL area, execute, not "recreate a temporary table and discard it," which is a reasonable conceptual simplification but not what actually happens.
• Merging is the default but not universal; a RESULT_CACHE-hinted view is a concrete case where Oracle deliberately skips merging to keep the cached result reusable.
• Shared SQL area reuse saves parsing effort across sessions issuing similar statements against the same view; it has nothing to do with caching the view's data or result.
• There is typically no distinct "view table" object sitting in memory at all; the merged statement executes directly against the base tables.
• Saving a view's output as a frozen table is a real anti-pattern, and a genuinely different thing from a materialized view, which has its own managed refresh mechanism.
• Writes against an updatable view follow the same underlying principle as reads: they translate into operations against the base table, since that's the only place data physically exists.

The next lesson in this module builds on this same foundation, extending from how a single view is called into the broader set of things views make possible once that basic mechanism is second nature.