Database Reference
In-Depth Information
count(*) AS count_star,
count(quantity_sold) AS count_quantity_sold,
count(amount_sold) AS count_amount_sold,
dbms_mview.pmarker(s.rowid)
AS pmarker
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,
dbms_mview.pmarker(s.rowid)
■
Because the
pmarker
function is called for each row, don't underestimate the time needed to call it. on my
system, creating the materialized view takes 2.5 times longer with the partition marker than without it.
Note
The materialized view contains a join-dependent expression when one of the columns referenced in the
SELECT
clause belongs to a table that is joined through an equality predicate based on the partition key. In the example used
in this section, it means that not only does an equi-join with the
times
table have to be added to the materialized
view (
s.time_id = t.time_id
), but one of the columns of the
times
table is also added to the
SELECT
and
GROUP BY
clauses (
t.fiscal_year
). Here's an example:
CREATE MATERIALIZED VIEW sales_mv
REFRESH FORCE ON DEMAND
AS
SELECT p.prod_category, c.country_id,
t.fiscal_year
,
sum(quantity_sold) AS quantity_sold,
sum(amount_sold) AS amount_sold,
count(*) AS count_star,
count(quantity_sold) AS count_quantity_sold,
count(amount_sold) AS count_amount_sold
FROM sales s, customers c, products p,
times t
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND
s.time_id = t.time_id
GROUP BY p.prod_category, c.country_id,
t.fiscal_year
With either the partition marker or the join-dependent expression in place, an analysis using the
explain_mview
function in the
dbms_mview
package shows that a fast refresh based on partition change tracking is possible. However,
it's possible only for modifications performed on the
sales
table:
SQL> SELECT capability_name, possible, msgtxt, related_text
2 FROM mv_capabilities_table
3 WHERE statement_id = '43'
4 AND capability_name IN ('PCT_TABLE','REFRESH_FAST_PCT');
CAPABILITY_NAME POSSIBLE MSGTXT RELATED_TEXT
---------------- -------- ----------------------------------- ------------
PCT_TABLE Y SALES
PCT_TABLE N relation is not a partitioned table CUSTOMERS
PCT_TABLE N relation is not a partitioned table PRODUCTS
REFRESH_FAST_PCT Y
