Database Reference
In-Depth Information
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,
p.prod_status
To display the query rewrite methods supported by a materialized view, you can query the
user_mviews
view
(the
dba
,
all
, and, in a 12.1 multitenant environment,
cdb
related views can also be used) as shown in the following
example. In this case, according to the
rewrite_enabled
column, query rewrite is enabled at the materialized view
level, and according to the
rewrite_capability
column, only text-match query rewrite is supported (in other words,
general query rewrite isn't):
SQL> SELECT rewrite_enabled, rewrite_capability
2 FROM user_mviews
3 WHERE mview_name = 'SALES_MV';
REWRITE_ENABLED REWRITE_CAPABILITY
--------------- ------------------
Y TEXTMATCH
Note that the
rewrite_capability
column can have only one of the following values:
none
,
textmatch
, or
general
. If
general
query rewrite is supported (and, consequently, the other two methods as well), the information
provided by the
user_mviews
view is enough. However, as in this case, if the value
textmatch
is shown, it would
be useful to know at least two more things. First, which of the two types of text-match query rewrite is supported?
Only full-text-match query rewrite or also partial-text-match query rewrite? Second, why isn't general query rewrite
supported?
To answer these questions, you can use the
explain_mview
procedure in the
dbms_mview
package, as shown in
the following example. Notice that the
mv
parameter specifies the name of the materialized view, and the
stmt_id
parameter specifies an arbitrary string used to identify the information stored in the output table
mv_capabilities_table
:
SQL> execute dbms_mview.explain_mview(mv => 'sales_mv', stmt_id => '42')
■
The
mv_capabilities_table
table isn't available per default. You can create it in the schema used for the
analysis by executing the
utlxmv.sql
script stored under
$ORACLE_HOME/rdbms/admin
.
Note
The output of the procedure, found in the
mv_capabilities_table
table, shows whether the
sales_mv
materialized view supports the three query rewrite modes. If it doesn't, the
msgtxt
column indicates the reason why
a specific query rewrite mode isn't supported. In this case, notice that the problem is caused by at least one column
that's referenced in the
GROUP BY
clause only (upon inspecting the SQL statement, you can immediately identify the
column causing the problem:
p.prod_status
):
SQL> SELECT capability_name, possible, msgtxt
2 FROM mv_capabilities_table
3 WHERE statement_id = '42'
4 AND capability_name IN ('REWRITE_FULL_TEXT_MATCH',
5 'REWRITE_PARTIAL_TEXT_MATCH',
6 'REWRITE_GENERAL');
