Hi,
my oracle db : 11G
I have a requirement and tried to solve it using materialized views, my complete refresh is working fine but I would like to do a fast refresh of my materialized view, here is some more information:
1) consider the table to be sh.sales [the sample table that comes with oracle installation in sh schema]
PS: i have created a table sh_sales4 which is an exact copy of sh.sales, every object I am talking about exists in a schema 'plsqluser2' and this user has all the necessary privs which are related to materialized views.
I have created a materialized view log on the table sh_sales4:
create materialized view log on sh_sales4
with rowid
including new values;
2) i have created a materialized view on my sh_sales4 table as :
create materialized view mv_sh_sales4_chanid_agg
refresh complete
with rowid
enable query rewrite
as
select channel_id, sum(amount_sold)
from sh_sales4
group by channel_id;
-- after its creation, i change the refresh option from complete refresh to force refresh, force refresh gives me an option to fast refresh.
alter materialized view mv_sh_sales4_chanid_agg_count refresh force;
3) now i update a row in sh_sales4 and insert 1 new row.
PS: I manage to see these 2 entries in my materialized view log table
4) Now when i try to fast refresh using :
exec dbms_mview.refresh('mv_sh_sales4_chanid_agg_count','F');
its not working for me and I am getting an exception :
--
Error report -
ORA-12033: cannot use filter columns from materialized view log on "PLSQLUSER2"."SH_SALES4"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
12033. 0000 - "cannot use filter columns from materialized view log on \"%s\".\"%s\""
*Cause: The materialized view log either did not have filter columns
logged, or the timestamp associated with the filter columns was
more recent than the last refresh time.
*Action: A complete refresh is required before the next fast refresh.
Add filter columns to the materialized view log, if required.
--
however the complete refresh is working.
Now the thing is my purpose of creating a materialized view is to get some good performance leverage that we get from materialized views, could any1 please tell me if a fast refresh is achievable in this case or not.
Some additional information:
1) on the base table [sh_sales4], there will be inserts and updates only
2) the materialized view is going to be refreshed manually, materialized view would be refreshed once every day, so lets say every day 9 am - 5pm there would be inserts and updates to the sh_sales4 table and once post 5 pm a fast refresh will take place.
I have read the manuals 2-3 times but I am a bit confused as there are many things to consider when aggregation is happening for your materialized view but at the same time my example is very simple, I am under impression that this is doable with materialized views and mv's must be used with these kind of activities.
PS: I am new to the topic of Mv's so please help me.
Regards
Rahul