What is the difference between a view and a materialized view in Oracle?

A view does not store data physically and executes the underlying query each time it is accessed, while a materialized view stores the query result as a physical table. 2. Materialized views generally offer faster query performance because they access precomputed data, whereas views can be slower as they execute the query on demand. 3. Views always return up-to-date data from base tables, but materialized views may contain stale data and require refreshes via ON COMMIT, ON DEMAND, or FAST/COMPLETE methods to stay current. 4. Use views for real-time data access and simplified querying with low storage overhead, and use materialized views for performance-critical scenarios like reporting or remote data replication where some data latency is acceptable. 5. Views require no additional storage and minimal maintenance, while materialized views consume disk space and need ongoing refresh management. In summary, the choice depends on whether data freshness or query speed is more important, with views favoring currency and materialized views favoring performance.

In Oracle, both views and materialized views provide a way to store a query for reuse, but they work differently in terms of data storage, performance, and maintenance.

1. How Data is Stored

• View (Standard View):
  A view is a virtual table based on the result of a SQL query. It does not store data physically. Every time you query a view, Oracle runs the underlying query against the base tables and returns the current data.

• Materialized View:
  A materialized view does store the actual result set of the query in physical storage (like a table). It holds a snapshot of the data at a point in time.

  

2. Performance and Query Speed

• View:
  Since the query runs every time the view is accessed, performance depends on the complexity of the query and the size of the underlying tables. If the base tables are large or the query involves joins or aggregations, performance can be slow.

• Materialized View:
  Queries against a materialized view are generally faster because Oracle reads from the precomputed, stored data rather than re-executing the full query each time.

  

3. Data Freshness

• View:
  Always returns up-to-date data because it reflects the current state of the base tables.

• Materialized View:
  Data may be stale. It only reflects the data as of the last refresh. You need to refresh it manually or on a schedule to get updated data.

  Refresh options include:

  • ON COMMIT: Refreshes every time a transaction commits on the source tables (real-time sync, but can impact performance).
  • ON DEMAND: Refreshed manually or via a job.
  • FAST or COMPLETE refresh: FAST applies only changes (incremental), COMPLETE rebuilds the entire view.

4. Use Cases

• Use a View when:

  • You need real-time data.
  • The query logic is complex, but performance is acceptable.
  • You want to simplify access to joined or filtered data without duplicating storage.

• Use a Materialized View when:

  • You need fast query performance on complex aggregations or joins.
  • Real-time data isn’t critical (e.g., reporting, data warehousing).
  • You’re working with remote databases (materialized views are often used for replication).

5. Storage and Maintenance

• View:
  No extra storage needed. Low maintenance.

• Materialized View:
  Requires disk space to store the data. Needs a refresh strategy and may require indexes for performance.

In short:
A view is a saved query that runs live each time.
A materialized view is a saved result set that must be refreshed.

Choose based on whether you prioritize freshness (use view) or performance (use materialized view).

Basically, it's a trade-off between data currency and speed.

The above is the detailed content of What is the difference between a view and a materialized view in Oracle?. For more information, please follow other related articles on the PHP Chinese website!