I understand that this is a Bad Thing in some circumstances, but there are very clear circumstances in which it is not -- the key characteristic of the circumstances in which it is Not a Bad Thing is where the materialized view does not preserve individual records from the master table (because it aggregates them), and where is does not have to access the master table to perform the refresh (see below).
Unfortunately (for me, and surely for others) this is a circumstance which is prevalent in data warehousing -- search Oracle Documentation for "Materialized View" and a great many hits come from the Data Warehousing Guide, and as I mentioned before this "Bad Thing" is a configuration that is widely advocated in that document.
Anyhoo, "the fast refresh most certainly does touch the master table -- why do you believe otherwise?". Two reasons -- the documentation implies it, and in the true spirit of scientific enquiry I have tested it. Here's the documentation ...
[QUOTE]
Restrictions on Fast Refresh on Materialized Views with Aggregates
Defining queries for materialized views with joins and aggregates have the following restrictions on fast refresh:
* All restrictions from "General Restrictions on Fast Refresh".
Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:
* All tables in the materialized view must have materialized view logs, and the materialized view logs must:
o Contain all columns from the table referenced in the materialized view.
o Specify with ROWID and INCLUDING NEW VALUES.
o Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
[/QUOTE]
The inclusion of "Contain all columns from the table referenced in the materialized view." implies that the fast refresh is performed without reference to the master table -- i see no other reason for it's inclusion.
However, here is a script that i believe demonstrates it.
SQL> CREATE TABLESPACE "SPONGEBOB"
2 LOGGING
3 DATAFILE 'C:\ORACLE\ORADATA\LOCAL\SPONGEBOB1.ora' SIZE 1M
4 REUSE AUTOEXTEND
5 ON NEXT 500K MAXSIZE 5M EXTENT MANAGEMENT LOCAL
6 SEGMENT SPACE MANAGEMENT AUTO
7 /
Tablespace created.
SQL> CREATE TABLE FACT
2 (
3 store_key number(8),
4 prod_key number(6),
5 time_key number(8),
6 dollar_sales number(12)
7 )
8 tablespace spongebob;
Table created.
SQL> CREATE MATERIALIZED VIEW log on FACT
2 tablespace sandbox
3 with rowid (store_key,time_key,dollar_sales)
4 including new values;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW FACT_STORE_TIME_MV
2 tablespace sandbox
3 build immediate
4 refresh fast on demand
5 enable query rewrite
6 as
7 select
8 store_key,
9 time_key,
10 sum(dollar_sales) sum_dollar_sales,
11 count(dollar_sales) c_dollar_sales,
12 count(*) c_star
13 from
14 FACT
15 group by
16 store_key,
17 time_key;
Materialized view created.
SQL> set feedback off
SQL> insert into FACT values (1,1,1,1000);
SQL> insert into FACT values (1,1,2,1234);
SQL> insert into FACT values (1,1,3,2345);
SQL> insert into FACT values (1,2,1,1111);
SQL> insert into FACT values (1,2,2,2222);
SQL> insert into FACT values (1,2,3,3333);
SQL> insert into FACT values (2,1,1,2132);
SQL> insert into FACT values (2,1,2,3244);
SQL> insert into FACT values (2,1,3,2167);
SQL> insert into FACT values (2,2,1,8901);
SQL> insert into FACT values (2,2,2,1114);
SQL> insert into FACT values (2,2,3,8890);
SQL> commit;
SQL> set feedback on
SQL> select * from FACT_STORE_TIME_MV;
no rows selected
SQL> alter tablespace spongebob offline;
Tablespace altered.
SQL> select * from FACT;
select * from FACT
*
ERROR at line 1:
ORA-00376: file 23 cannot be read at this time
ORA-01110: data file 23: 'C:\ORACLE\ORADATA\LOCAL\SPONGEBOB1.ORA'
SQL> begin
2 DBMS_MVIEW.REFRESH
3 (
4 list => 'FACT_STORE_TIME_MV',
5 method => 'f',
6 push_deferred_rpc => false
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select * from FACT_STORE_TIME_MV;
STORE_KEY TIME_KEY SUM_DOLLAR_SALES C_DOLLAR_SALES C_STAR
---------- ---------- ---------------- -------------- ----------
1 3 5678 2 2
1 2 3456 2 2
2 1 11033 2 2
2 2 4358 2 2
1 1 2111 2 2
2 3 11057 2 2
6 rows selected.
You see from the above that the master table was not available at the time that the refresh was requested -- hence i conclude that only the log was accessed.
Evidently there is an element within Oracle that thinks this scenario is supported, because they are documenting it as such.
January 25, 2004 - 6:49 pm UTC
In general, the defining tables are touched -- it is not the rule. It was the generalization:
"the fast
refresh process doesn't even touch the master table itself, and hence the
movement of the table is irrelevant to the refresh process."
i was have a problem with -- in general, that is not the case.
If ( you NEVER reorg the tables
AND
you do not enable row movement on these tables )
THEN
using rowids is safe
ELSE
using rowids is not safe. anything that stored rowids that pointed
to these objects is not safe.
end if
I never said it was supported.
You asked "i've been told this is not such a good idea, any ideas why"
I simply tried to respond with "this is why it can be a bad idea"
that's all. do what you will -- it obviously is supported (hey, you are doing it, the sql is right there -- it is documented)