Database Reference
In-Depth Information
view is based are called
base tables
(they're also known as
master tables
). The following SQL statement and
Figure
15-1
illustrate this (notice that the
ORDER BY
clause used in the original query is omitted):
CREATE MATERIALIZED VIEW sales_mv
AS
SELECT p.prod_category, c.country_id,
sum(quantity_sold) AS quantity_sold,
sum(amount_sold) AS amount_sold
FROM sales s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
Figure 15-1.
Creation of a materialized view
■
When you create a materialized view based on a query containing the
ORDER BY
clause, the rows are sorted
according to the
ORDER BY
clause only during the creation of the materialized view. Later, during refreshes, this sorting
criterion isn't maintained. This is also because the
ORDER BY
clause isn't included in the materialized view's definition that
is stored in the data dictionary.
Note
When you execute the previous SQL statement, the database engine creates a materialized view (which is only an
object in the data dictionary—in other words, it's only metadata) and a
container table
. The container table is a regular
heap table that has the same name as the materialized view. It's used to store the result set returned by the query.
You can query the container table as you would with any other table. The following SQL statement shows an
example of this:
SELECT *
FROM sales_mv
ORDER BY prod_category, country_id
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 81 | 3 |
| 1 | SORT ORDER BY | | 1 | 81 | 81 | 3 |
| 2 | MAT_VIEW ACCESS FULL| SALES_MV | 1 | 81 | 81 | 3 |
