Oracle Materialized view
According to the oracle docs, we cannot use the fast update method to update the materialized view of the aggregate. I found this example in the Oracle docs: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm
CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE (time_id, calendar_year)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (prod_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
every time i tried to use aggregations and update quickly with each other i was getting error.
Are there any special tips when using the fast update and aggregation functions with each other?
respectfully
+3
source to share
1 answer
According to my survey, in order to create an MV with aggregation and fast refresh functionality, your MV and MV log must have special structures to see the correct MV and MV log run structures under the scripts:
begin
dbms_advisor.tune_mview(task_name=>:t,
mv_create_stmt=>'create materialized view mv1 refresh fast
as select job,sum(sal) from emp group by job');
end;
then run below query to see the desired structures for MV and MV logs:
select dbms_lob.substr( statement, 4000, 1 ), statement from user_tune_mview
where task_name='TASK_2042' order by action_id;
+1
source to share