Database Reference
In-Depth Information
The following SQL statement shows an example that's based on the same query as earlier, but where several of
the parameters that have just been described are specified:
CREATE MATERIALIZED VIEW sales_mv
PARTITION BY HASH (country_id) PARTITIONS 8
TABLESPACE users
BUILD IMMEDIATE
USING NO INDEX
ENABLE QUERY REWRITE
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
In addition, you can also specify how the materialized view is refreshed. The “Materialized View Refreshes”
section provides detailed information on this topic.
Query Rewrite
The query optimizer is able to take advantage of query rewrite whenever a
SELECT
clause is present in a SQL
statement, or, to be more specific, in the following cases:
•
SELECT ... FROM ...
•
CREATE TABLE ... AS SELECT ... FROM ...
•
INSERT INTO ... SELECT ... FROM ...
Subqueries
•
In addition, as already described, query rewrite is used only when two requirements are fulfilled. First, the
query_rewrite_enabled
initialization parameter must be set to
TRUE
(the default value). Second, the materialized
view must be created with the
enable query rewrite
parameter.
Once these requirements are met, every time the query optimizer generates an execution plan, it has to find out
whether a materialized view that contains the required data can be used to rewrite a SQL statement. For that purpose,
it uses one of three methods:
•
Full-text-match
query rewrite
: The text of the query passed to the query optimizer is compared
to the text of the query of each available materialized view. If they match, the materialized
view obviously contains the required data. Note that the comparison is less strict than the one
commonly used by the database engine: it's case insensitive (except for literals) and ignores
blank spaces (for example, new lines and tabs) and the
ORDER BY
clause.
•
Partial-text-match
query rewrite
: The comparison is similar to the one used for full-text-match
query rewrite. With this one, however, differences in the
SELECT
clause are permitted. For
example, if the materialized view stores three columns and only two of them are referenced by
the query to be optimized, the materialized view contains all the required data, and therefore
a query rewrite is possible.
