Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

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.?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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:

https://www.experts-exchange.com/questions/21326548/Bypassing-Materialized-view-primary-key-constraint.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
Hi,
A materialized view is a table. You can create constraints on it, as well as indexes.
Regards,
Franck.
>>You can create constraints on it, as well as indexes.

With the exception of a Primary Key constraint.
Right, and unique indexes may cause problems as well (Metalink note 284101.1)
Regards,
Franck.
Avatar of toooki

ASKER

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).
Create a unique index on the view.  

The only real difference is a unique index will allow nulls and a primary key will not.
ASKER CERTIFIED SOLUTION
Avatar of Franck Pachot
Franck Pachot
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of toooki

ASKER

Thanks all for the help. I learned quite a few new matters.
Thank you.