Database Reference
In-Depth Information
although, from a user perspective, there's no difference between the two, the algorithm used for fast refreshes by
materialized view logs based on commit SCn numbers can lead to better performance.
note that materialized view logs based on commit SCn numbers aren't enabled by default because they're
subject to some limitations. For example, tables with LoB columns aren't supported.
To solve the problems related to the materialized view, some new columns, based on the
count
function, have
to be added to the query associated to the materialized view. The following SQL statement shows the definition that
includes the new columns:
CREATE MATERIALIZED VIEW sales_mv
REFRESH FORCE ON DEMAND
AS
SELECT p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold,
sum(s.amount_sold) AS amount_sold,
count(*) AS count_star,
count(s.quantity_sold) AS count_quantity_sold,
count(s.amount_sold) AS count_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
After redefining the materialized view logs and the materialized view, a further analysis using the
explain_mview
procedure shows that fast refreshes are possible in all situations (the
possible
column is always set to
Y
). So, let's test
how fast the refresh is by inserting data into two tables and then executing a fast refresh:
SQL> INSERT INTO products
2 SELECT 619, prod_name, prod_desc, prod_subcategory, prod_subcategory_id,
3 prod_subcategory_desc, prod_category, prod_category_id,
4 prod_category_desc, prod_weight_class, prod_unit_of_measure,
5 prod_pack_size, supplier_id, prod_status, prod_list_price,
6 prod_min_price, prod_total, prod_total_id, prod_src_id,
7 prod_eff_from, prod_eff_to, prod_valid
8 FROM products
9 WHERE prod_id = 136;
SQL> INSERT INTO sales
2 SELECT 619, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold
3 FROM sales
4 WHERE prod_id = 136;
SQL> COMMIT;
SQL> execute dbms_mview.refresh(list => 'sh.sales_mv', method => 'f')
Elapsed: 00:00:00.12
