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.?
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.?
Hi,
A materialized view is a table. You can create constraints on it, as well as indexes.
Regards,
Franck.
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.
With the exception of a Primary Key constraint.
Right, and unique indexes may cause problems as well (Metalink note 284101.1)
Regards,
Franck.
Regards,
Franck.
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.
The only real difference is a unique index will allow nulls and a primary key will not.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all for the help. I learned quite a few new matters.
Thank you.
Thank you.
>>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