[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Primary Key in Oracle 11g Materialized view

Posted on 2011-03-09
9
Medium Priority
?
1,163 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 952 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 948 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

649 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