• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • Last Modified:

Primary Key in Oracle 11g Materialized view

When I create a Materialized view -- do queries on the Materialized view as efficient as the queries on a table with the same content of the  Materialized view?
I created a Materialized view:
-----------------
CREATE MATERIALIZED VIEW MYMATVIEW1
REFRESH FORCE ON DEMAND
START WITH SYSDATE NEXT SYSDATE + 1/24
AS
SELECT  X, Y, Z
FROM .....
-----------------

How can I set the X field of the Materialized view as the primary key of the Materialized view? If I had a table in the same place, I could easily create a primary key for the field X on the same table.

Also how do I query the database to see when the Materialized view was last updated and how long it took to run/etc.?
0
toooki
Asked:
toooki
  • 4
  • 3
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
You really should ask a single question per question.

>>view as efficient as the queries on a table with the same content of the  Materialized view?

All things being equal, yes.  A MV is just another database object.  It should perform just like any other table.

>>field of the Materialized view as the primary key

I've never tried to create a PK on an MV but you can use the PK from the base table:

http://www.experts-exchange.com/Database/Oracle/Q_21326548.html

>>lso how do I query the database to see when the Materialized view was last updated

Check DBA_MVIEW_REFRESH_TIMES

>>and how long it took to run/etc.?

Not sure what you are after here
0
 
Franck PachotCommented:
Hi,
A materialized view is a table. You can create constraints on it, as well as indexes.
Regards,
Franck.
0
 
slightwv (䄆 Netminder) Commented:
>>You can create constraints on it, as well as indexes.

With the exception of a Primary Key constraint.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Franck PachotCommented:
Right, and unique indexes may cause problems as well (Metalink note 284101.1)
Regards,
Franck.
0
 
toookiAuthor Commented:
Thank you for all help. The only reason I am concerned about is the performance of the queries on the materialized view without primary key value. The materialized view is based on a complex query and it has 70K records in it. How does the source table PK stays in the materialized view? All I want is to make queries on the materialized view faster (as fast as querying similar table with a primary key).
0
 
slightwv (䄆 Netminder) Commented:
Create a unique index on the view.  

The only real difference is a unique index will allow nulls and a primary key will not.
0
 
Franck PachotCommented:
Hi,

>>>>You can create constraints on it, as well as indexes.
>> With the exception of a Primary Key constraint

Yes you can. But it can be automatically created:


SQL> create table t as select rownum n from dba_objects;

Table created.

SQL> alter table t add constraint t_pk primary key(n);

Table altered.

SQL> create materialized view mv as select * from t;

Materialized view created.

SQL> select table_name,index_name from user_indexes where table_name in ('T','MV');

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
MV                             T_PK1
T                              T_PK

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
For simple MV's, yes.

Try a complex one crossing two tables.
drop table t1 purge;
drop table t2 purge;

create table t1 as select rownum n from dba_objects where rownum=1;
create table t2 as select rownum n from dba_objects where rownum=1;

alter table t1 add constraint t1_pk primary key(n);
alter table t2 add constraint t2_pk primary key(n);

create materialized view mv as select * from t1 union all select * from t2;

select table_name,index_name from user_indexes where table_name in ('T1','T2','MV');

T1                             T1_PK
T2                             T2_PK

Open in new window

0
 
toookiAuthor Commented:
Thanks all for the help. I learned quite a few new matters.
Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now