Using optimizations from a subquery
You can create a query having a subquery that the optimizer rewrites based on a materialized view. You create a materialized view, and then run a query that uses the materialized view.
In this task, you create a materialized view and use it in a subquery to return the number of destination-origin pairs. Suppose the data resides in a table named
flights_datathat has the following columns:
Create a table schema definition named flights_data for destination and origin
CREATE TABLE flights_data( c_id INT, dest VARCHAR(256), origin VARCHAR(256));
Create a materialized view that counts destinations and origins.
CREATE MATERIALIZED VIEW mv1 AS SELECT dest, origin, count(*) FROM flights_data GROUP BY dest, origin;
Take advantage of the materialized view to speed your queries when you have to
count destinations and origins again.
For example, use a subquery to select the number of destination-origin pairs like the materialized view.
SELECT count(*)/2 FROM( SELECT dest, origin, count(*) FROM flights_data GROUP BY dest, origin ) AS t;Transparently, the SQL engine uses the work already in place since creation of the materialized view instead of reprocessing.