Skip to Main Content
  • Questions
  • Materialized View on Table Without Primary Key

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: January 24, 2004 - 8:05 pm UTC

Last updated: March 24, 2004 - 10:43 am UTC

Version: 9.1.2

Viewed 10K+ times! This question is

You Asked

Tom,

Is there any reason why it would be necessary for the master table of a materialized view to have a primary key in order that fast refresh work?

For example...

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 );

Table created.

SQL> CREATE MATERIALIZED VIEW log on FACT
2 with rowid (store_key,time_key,dollar_sales)
3 including new values;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW FACT_STORE_TIME_MV
2 build immediate
3 refresh fast on demand
4 enable query rewrite
5 as
6 select
7 store_key,
8 time_key,
9 sum(dollar_sales) sum_dollar_sales,
10 count(dollar_sales) c_dollar_sales,
11 count(*) c_star
12 from
13 FACT
14 group by
15 store_key,
16 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> 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.

This works fine, but I've been told that this usage is A Bad Thing because the master table has to have a primary key.

Any thoughts on why this might be so?

and Tom said...

because more and more often rowids can change.

In 9i, urowids on IOTs change. Partitioned tables with enable row movement allow rowids to change. ALTER TABLE t MOVE will change rowids. In 10g more and more things will change rowids.

So, a rowid is no longer this imutable key that is assigned when a row is inserted and kept with the row forever.

That is why it is bad -- rowids can change and they will not be "captured" during the refresh process. You cannot rebuild, reorg or allow row movement on tables where the rowid cannot change. It is very inflexible and getting even more so (lots of new features in 10g rely on row movement and rowids changing -- such as flashback table and online segment shrinking)

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dave, January 25, 2004 - 3:47 pm UTC

Can you give an example of how a change to a rowid in the master table could "break" the fast refresh process? I'm stumped to think of a way this could happen. It seems to me that once a change has been logged in the MV log, row movement in the base table would not be an issue -- fast refresh would not be affected?

Tom Kyte
January 25, 2004 - 4:11 pm UTC

alter table t move;


bamm. all rowids changed, nothing in the mv logs.


update a partition key, cause the row to move, bamm. the rowid based changes COUNT on the rowids being imutable (they were when snapshots were invented in 7.0, they stopped being so in 8i)

As every table should in fact have a primary key -- the use of rowids is frowned upon for this reason -- and the fact that reorgs and many admin operations will totally kill it.

Dave, January 25, 2004 - 4:45 pm UTC

Well, that's an interesting "feature"! I commonly design data warehouse fact tables to have no primary key, because the logical key tends to be so many columns that it is unfeasible (say 12 columns out of a table of 16 columns total), and a synthetic key has to be prefixed by the partition keys, so it's still no lightweight single-column index.

If it weren't that Oracle automatically removes entries from the rowid-based log ("bamm", as you say) then the rowid MV log would hold all the information needed for fast refresh of summary tables (due to the minimum conditions that the log must meet in order for the MV to be created as fast refreshable) -- 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'd agree that the rowid change would not be irrelevant were I trying to create an updateable materialized view througha rowid-based log, henve the change to PK-basedlogs, but that's not even close to an option on a summary MV of the type I showed.

By the way, the Oracle Data Warehousing Guide is full of examples of non-PK'd master tables with fast refresh through a rowid-based log. The "Sales History" example schema is used throughout, and the sales fact table is not PK'ed.

Tom Kyte
January 25, 2004 - 5:36 pm UTC

it is administrative and row moving thing that mess it up. things that DO NOT, CANNOT maintain the log.


we do not automatically remove things, it is that things are not put there - we are assuming "rowid is imutable, never changing" -- however, rowid is changing and many more things into the future will cause it to in fact change.


the fast refresh most certainly does touch the master table -- why do you believe otherwise?

But -- it is because the ROWID CHANGES that we have a problem -- and there is no such log record as "rowid changed, please update your rowid in your MV"

you asked me a question with "This works fine, but I've been told that this usage is A Bad Thing because the
master table has to have a primary key."

I've tried to tell you why this falls into the category of "a bad idea".

Dave, January 25, 2004 - 6:26 pm UTC

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. 

Tom Kyte
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)




Dave, January 25, 2004 - 7:08 pm UTC

I suppose that the folks who wrote the documentation and coded the MV/MV log restrictions that promote/allow this technique had the narrow set of circumstances in mind in which this was a valid configuration, where changes to rowid would not affect functionality. The emptying of the rowid-based MV log when the master table is moved is unfortunately not documented as a limitation on the validity of the methodology.

It's enough for me to know that it's supported -- I'd been told that it isn't.

The (thinly) humorous part of all this is that I don't even want to use DML-based fast refresh -- what I'm doing is Partition Change Tracking (PCT) fast refresh. One of the peculiarities of PCT fast refresh is that you must have an MV log on the partitioned master table, even though you might never, ever use it -- all modifications to the master table are through partition exchange and other partition-based operations. It was during a request for assistance with a PCT problem that I was told that the non-PK'ed master table was not supported, and that I couldn't be helped further.

Is it possible that you can find out for me/us how Oracle tracks master table partitions that have changes to push to PCT MV's? I'm guessing that there is a comparison of SCN's for the last MV refresh and that of the partition changes, but it appears to be undocumented.

Lastly, thanks muchly for the help on a Sunday. It's well above and beyond the call of duty, and it's much appreciated.

OK

Stephen, January 26, 2004 - 8:39 am UTC

Hello sir,
While creating materialized views What is the use of using
the clause 'enable query rewrite'?Could you please explain?
Thanks in advance.


Tom Kyte
January 26, 2004 - 9:12 am UTC

query rewrite allows Oracle to rewrite your query against some detail table to be against the materialized view. It lets Oracle rewrite the query. consider:

big_table@ORA920PC> alter session set query_rewrite_enabled=true;
Session altered.

big_table@ORA920PC> alter session set query_rewrite_integrity=enforced;
Session altered.

big_table@ORA920PC> select status, count(*) from big_table group by status;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8867 Card=2 Bytes=12)
1 0 SORT (GROUP BY) (Cost=8867 Card=2 Bytes=12)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=4861 Card=3500000 Bytes=21000000)




Statistics
----------------------------------------------------------
1477 recursive calls
0 db block gets
50822 consistent gets
50543 physical reads
480 redo size
487 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
30 sorts (memory)
0 sorts (disk)
2 rows processed

big_table@ORA920PC> set autotrace off
big_table@ORA920PC> pause

big_table@ORA920PC>
big_table@ORA920PC> create materialized view big_table_mv
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select status, count(*)
7 from big_table
8 group by status
9 /

Materialized view created.

big_table@ORA920PC> analyze table big_table_mv compute statistics;

Table analyzed.

big_table@ORA920PC> pause

big_table@ORA920PC>
big_table@ORA920PC> variable x varchar2(20);
big_table@ORA920PC> exec :x := 'VALID';

PL/SQL procedure successfully completed.

big_table@ORA920PC>
big_table@ORA920PC> set termout off
big_table@ORA920PC> set autotrace traceonly
big_table@ORA920PC> select status, count(*) from big_table group by status;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE_MV' (Cost=2 Card=2 Bytes=22)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed



see how the query was silently rewritten to use a totally different table than you queried? that is query rewrite.


ROWID changes.. yes ... but arn't they safe in objects where they are used by defination

Shouvik Basu, January 27, 2004 - 9:47 am UTC

Thanks to Dave for posting the issue. I personally do not like the concept of a Surrogate Key neither a PK of 8 columns. However now I am inclining to Surrogate Keys (I am sure there will be a messup with rowid in database recovery situations).

The concept of ROWID change has been aptly explained by Tom. IOT or Partitioned tables with row movement or reorg. So "5 stars".

Just wanted to know
(1) ROWIDs are used in indexes, in MV logs, and probably in some other places. How can they ever change in a scalable way ? Any reorg of the table which impacts ROWIDs should implicitly mean a reorg of the indexes also. Or does Oracle have a new internal ID (which it wont share with us ;) )?

(2) "Online segment shrinking" - what are they ? Based on point 1, are they scalable ?

(3) Considering reorgs as DDLs and update of partition key and index key of IOT as DMLs, if I make the general statement
"ROWID OF A ROW DOES NOT CHANGE IF THE TABLE IT IS IN IS NOT IMPACTED BY A DML OR DDL".
Am I right ?

(4) Dave's example

"CREATE MATERIALIZED VIEW log on FACT
with rowid (store_key,time_key,dollar_sales)
including new values;"

has got "rowid" in the defination. A change in rowid of the base table should rebuild the log. I still feel as long as we are not storing ROWID as values in other columns it is safe.


Tom Kyte
January 27, 2004 - 10:34 am UTC

rowids won't get messed up in database recovery situations. recovery involves putting each bit back where it belongs. it is a 100% physical thing. It would be reorgs and other things that change rowids that would mess it up.

1/2) alter table t move implies you will be doing a "index rebuild". If you move or reorg the table, you will be doing a rebuild of indexes.

If you update a partition key -- and it moves from partition 1 to partition 2, it is processed much like a delete plus insert -- the index entry is maintained for you.

In 10g, many other things will perform rowid changes (flashback table, online segment shrink for example). These will maintain indexes.

So, it depends on the operation - some will cause you to rebuild (alter table move), others will maintain (shrink in 10g).


3) well, if a table is never affected by ddl or dml, table is empty.... but if all you do is INSERT and DELETE data, and never use ddl on the table -- rowids will be constant for a row as long as the row exists.

4) look at the log, it stores rowids in there. you cannot really "rebuild" a log.

It is "safe", it just prevents you from doing certain things or if you do them, can cause full refreshes to resync everything. If you use rowid as your primary key (hey -- you just created a surrogate for 8 columns there), you have to live with these facts.


materialized view log

sonali, March 24, 2004 - 9:53 am UTC

I have a MV and the materialized view log for fast refresh on one of them. I am writing a installation script for customers. I do not want them to get error on
DROP MATERIALIZED VIEW LOG ON xyz;
ORA-12000: a materialized view log already exists on table

if it does not exists initially.. so I was thinking of adding a check to see if it exists before doing the drop, but I cannot find which system table keeps the refrence to it ?

DROP MATERIALIZED VIEW LOG ON xyz;
CREATE MATERIALIZED VIEW LOG ON xyz WITH ROWID, SEQUENCE
(a,b,c)
INCLUDING NEW VALUES;

I have wrote a small pl/sql block to do this for view itself
DROP MATERIALIZED VIEW mv_xyz;
CREATE MATERIALIZED VIEW mv_xyz
....

Declare n int;
begin
select COUNT(*) into n from all_objects where OBJECT_NAME=upper('xyz') AND OBJECT_TYPE = 'MATERIALIZED VIEW' AND OWNER= UPPER('xxx');
If n=0 then
execute immediate 'DROP MATERIALIZED VIEW LOG ON xyz';
End if;
End;
/

How do I do something similar for mv logs ?

Thanks




Tom Kyte
March 24, 2004 - 10:43 am UTC

begin
execute immediate 'drop materialized view log on xyz';
exception
when others
if ( sqlcode = 12000 ) then null;
else raise;
end if;
end;
/

just try to do it and ignore the errors you wish to ignore.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library