Solved

Primary Key in Oracle 11g Materialized view

Posted on 2011-03-09
9
1,091 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 76

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 76

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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 76

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 76

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This video shows how to recover a database from a user managed backup
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now