Database Reference
In-Depth Information
2 l_query CLOB := 'SELECT upper(p.prod_category) AS prod_category,
3 sum(s.amount_sold) AS amount_sold
4 FROM sh.sales s, sh.products p
5 WHERE s.prod_id = p.prod_id
6 GROUP BY p.prod_category
7 ORDER BY p.prod_category';
8 BEGIN
9 DELETE rewrite_table WHERE statement_id = '42';
10 dbms_mview.explain_rewrite(
11 query => l_query,
12 mv => 'sh.sales_mv',
13 statement_id => '42'
14 );
15 END;
16 /
■
The
rewrite_table
table doesn't exist per default. You can create it in the schema used for the analysis by
executing the
utlxrw.sql
script stored under
$ORACLE_HOME/rdbms/admin
.
Note
The output of the procedure, provided in the
rewrite_table
table, gives the reasons why the query rewrite
doesn't happen. The output is composed of messages that are documented in the
Oracle Database Error Messages
manual. Here's the output of the previous analysis:
SQL> SELECT message
2 FROM rewrite_table
3 WHERE statement_id = '42';
MESSAGE
---------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01284: materialized view SALES_MV has an anchor table CUSTOMERS not found in query
QSM-01052: referential integrity constraint on table, SALES, not VALID in ENFORCED integrity
mode
It's also essential to understand that not all query rewrite methods can be applied to all materialized views.
Certain materialized views support full-text-match query rewrites only. Others support only full-text-match and
partial-text-match query rewrites. In general, as the complexity (given, for example, by the utilization of constructs
such as set operators and hierarchical queries) of materialized views increases, less often are advanced query rewrite
methods supported. The restrictions also depend on the Oracle Database version. So, instead of providing a list of
what is supported, I show you how to find out, given a specific case, which query rewrite methods are supported. To
illustrate, let's re-create the materialized view with the following SQL statement. Notice that, compared to the previous
examples, I have added only
p.prod_status
to the
GROUP BY
clause (in practice, executing such a SQL statement is
usually pointless, but, as you'll see shortly, it's an easy way to partially deactivate query rewrite):
CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE
AS
