Materialized View Log for Multiple Materialized Views
Environment:
HPUX 11.11
Oracle 9.2.0.7
I don't have a lot of experience using Materialized Views so I'd like some clarification if possible.
I have a couple of Views that users use heavily that represent self joining a table to itself from 6 to 10 times.
When called in a query, these views are adding some considerable overhead to the query run time.
Based on the answer to a previous question, I'm looking to turn them into Materialized Views.
Because they would be MV's based upon a self join, they are not considered "simple" MV's and can't be used for FAST REFRESH.
Can multiple MV's be kept updated from a single MV Log ? Will Oracle keep the updates in sync in multiple target MV's that are not simple and have to do a complete refresh ?
In the Oracle documentation (here), under the Managing Materialized View Log Space header, it talks about " multiple simple materialized views".
Since my MV's aren't "simple", is there anything I need to be aware of ?
I don't *think* there's going to be a concern, but would like to be sure before I try to implement.
Anyone have any experience with this kind of thing ?
Thanks in advance for any insight.
HPUX 11.11
Oracle 9.2.0.7
I don't have a lot of experience using Materialized Views so I'd like some clarification if possible.
I have a couple of Views that users use heavily that represent self joining a table to itself from 6 to 10 times.
When called in a query, these views are adding some considerable overhead to the query run time.
Based on the answer to a previous question, I'm looking to turn them into Materialized Views.
Because they would be MV's based upon a self join, they are not considered "simple" MV's and can't be used for FAST REFRESH.
Can multiple MV's be kept updated from a single MV Log ? Will Oracle keep the updates in sync in multiple target MV's that are not simple and have to do a complete refresh ?
In the Oracle documentation (here), under the Managing Materialized View Log Space header, it talks about " multiple simple materialized views".
Since my MV's aren't "simple", is there anything I need to be aware of ?
I don't *think* there's going to be a concern, but would like to be sure before I try to implement.
Anyone have any experience with this kind of thing ?
Thanks in advance for any insight.
Materialized view logs are not used for complete refreshes. They are only used for fast refreshes. Since your views are not "simple" and require a complete refresh every time, there is no need to create the materialized view log.
ASKER
There's no way to tell the materialized view to not refresh unless there has a been a change to the underlying data when using a complex MV ?
The structure I'm dealing with doesn't change very often. It seems like such a waste to rebuild the complete structure every hour when it might only change once a quarter if that.
My last part to this question, then, is what happens if a query attempts to execute against the MV while a rebuild is in progress (or vice versa, a rebuild starts while a query is running)?
I'm hoping that it will just hold a lock while the first process does it's thing and the second process to start will wait until the first operation completes and then complete successfully.
The structure I'm dealing with doesn't change very often. It seems like such a waste to rebuild the complete structure every hour when it might only change once a quarter if that.
My last part to this question, then, is what happens if a query attempts to execute against the MV while a rebuild is in progress (or vice versa, a rebuild starts while a query is running)?
I'm hoping that it will just hold a lock while the first process does it's thing and the second process to start will wait until the first operation completes and then complete successfully.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK maybe I can look at it that way. It doesn't take long for the refresh to complete, a matter of a few seconds, but because of Murphy's Law, you know that the users will want to query it in that short window.
Would need to modify the job that does the refresh to play synonym dancing, but that's doable.
I'll play with it and get back to you. Thanks.
Would need to modify the job that does the refresh to play synonym dancing, but that's doable.
I'll play with it and get back to you. Thanks.
ASKER
Thanks. This is how they want to proceed, so we'll go that way.
mv log is specified at the time of creation of mv
for ex:
CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID (dept_id, emp_id, ename,startdate) INCLUDING NEW VALUES;