Databases Reference
In-Depth Information
Materialized views can be seen as snapshots of the data in one or more tables, on which
a computation has been applied, for example, a join or a group. This summary data can be
used to answer client queries readily, instead of reading all the data in the original table(s).
An example is worth a thousand words. For example, we have a SALES table in SH schema,
containing around 1 million rows, and we want a report of sales by product. We will see how
materialized views, in such cases, can help a lot in reducing access to the database, specially
the I/O.
How to do it...
We will use SQL*Plus to test a simple script:
1.
Connect to the database TESTDB as user SH and execute a simple query on the
sales data, collecting statistics, timing, and execution plan:
CONNECT sh@TESTDB/sh
SET AUTOTRACE ON
SET TIMING ON
SELECT PROD_ID, SUM(AMOUNT_SOLD) FROM SH.SALES GROUP BY PROD_ID;
SET TIMING OFF
SET AUTOTRACE OFF
2.
Create a materialized view with the following statement:
CREATE MATERIALIZED VIEW SH.MV_SALES_BY_PRODUCT
BUILD IMMEDIATE REFRESH ON COMMIT
ENABLE QUERY REWRITE AS
SELECT PROD_ID, SUM(AMOUNT_SOLD) AS AMOUNT_SOLD FROM SH.SALES
GROUP BY PROD_ID;
3.
Analyze the materialized view to let the optimizer use it if needed:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MV_SALES_BY_PRODUCT',
estimate_percent => 100, method_opt => 'for all columns size 1');
4.
Execute the same query as mentioned in step 1:
SET AUTOTRACE ON
SET TIMING ON
SELECT PROD_ID, SUM(AMOUNT_SOLD) FROM SH.SALES GROUP BY PROD_ID;
SET TIMING OFF
SET AUTOTRACE OFF
Review the results.
