Solved

Primary Key in Oracle 11g Materialized view

Posted on 2011-03-09
9
1,119 Views
Last Modified: 2012-05-11
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
Comment
Question by:toooki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35089411
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 35093909
Hi,
A materialized view is a table. You can create constraints on it, as well as indexes.
Regards,
Franck.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35095259
>>You can create constraints on it, as well as indexes.

With the exception of a Primary Key constraint.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Franck Pachot
ID: 35097802
Right, and unique indexes may cause problems as well (Metalink note 284101.1)
Regards,
Franck.
0
 

Author Comment

by:toooki
ID: 35098560
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35098715
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
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 238 total points
ID: 35107858
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 237 total points
ID: 35109075
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
 

Author Comment

by:toooki
ID: 35226874
Thanks all for the help. I learned quite a few new matters.
Thank you.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question