Solved

Primary Key in Oracle 11g Materialized view

Posted on 2011-03-09
9
1,127 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

687 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