What are materialized views

Materialized view is like a view but stores both definition of a view plus the rows resulting from execution of the view. It uses a query as the bases and the query is executed at the time the view is created and the results are stored in a table. You can define the Materialized view with the same storage parameters as any other table and place it in any table space of your choice. You can also index and partition the Materialized view table like other tables to improve performance of queries executed against them.

The main concept is to reduce the execution time of long running query, bu summarizing data in the database.

CREATE MATERIALIZED VIEW my_all_objects_aggs
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
  SELECT owner, count(*)
    FROM my_all_objects
   GROUP BY owner;

Uses of Materialized View

1. Less physical reads - There is less data to scan through.
   
2. Less writes - We will not be sorting/aggregating as frequently.
   
3. Decreased CPU consumption - We will not be calculating aggregates and functions on the data, as we will have already done that.
   
4. Markedly faster response times - Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is not out of the question.